select last value from each Records and bind to Gridview

Question

0.00/5 (No votes)

See more:

The records (with delimiter) in my Database look like this:
100
100/1
100/2
101
101/1
102
102/1
102/2

My problem is to find a way to select the last values from each record and bind them to a Gridview (vb.net).
I want the output to look like the following:
100/2
101/1
102/2 ..

Regards
Anas

Posted 9-Mar-14 15:09pm

Member 10529245

Updated 10-Mar-14 5:51am

Rage

v3

Add a Solution

Comments

Rage 10-Mar-14 9:18am

Databases are not my cup of tea, but what's wrong in fetching these records with a SQL statement ?

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 10-Mar-14 10:06am

What have you tried and where is the problem?

Member 10529245 10-Mar-14 20:44pm

Thanks for your effort... my problem is need to retrieve all the maximum value from each record in mysql... from that above example i want the out put will be
1.100/2
2.101/1
3.102/2 how to retrieve all the rows based on this maximum value .. that max row has been going to bind in Gridview ....

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-03-10T06:21:00

The problem with that is that "100/1" is a string based value, so it will work with a string based comparison, rather than numeric. You can do it, but it's pretty cumbersome in SQL - I'd do it in a "proper" programing language where it's pretty simple.

SQL

SELECT MAX(ID) FROM MyTable
GROUP BY SUBSTRING(ID, 0, CASE WHEN CHARINDEX('/', ID) > 0
                               THEN CHARINDEX('/', ID)
                               ELSE LEN(ID)
                          END)

If you can change your DB so it's two fields, it becomes a lot easier!

Maciej Los · Answer 2 · 2014-03-10T08:58:00

Alternativelly, you can use REPLACE[^] function.

SQL

DECLARE @tmp TABLE (StringNumbers VARCHAR(10))

INSERT INTO @tmp (StringNumbers)
VALUES('100'), ('100/1'),
('100/2'), ('101'),
('101/1'), ('102'),
('102/1'), ('102/2'),
('103'), ('104')


SELECT StringNumbers, CONVERT(FLOAT, REPLACE(StringNumbers ,'/','.')) AS MyNumber
FROM @tmp
ORDER BY CONVERT(FLOAT, REPLACE(StringNumbers ,'/','.'))

Think of it and change the code to your needs ;)

Member 10529245 · Answer 3 · 2014-03-10T22:23:00

Solution 3

Finally i find the Solution that is

SELECT col1,col2,col3 FROM Tableone WHERE
Id=(SELECT MAX(ID) FROM Tableone i WHERE i.empname=Tableone.Empname)

Posted 10-Mar-14 22:23pm

Member 10529245

select last value from each Records and bind to Gridview

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0