SQL Query Filtering - PLease Help, Thank you

TABLEI have a table with data R. Virgo (3 Rows). I want to query the most recent only the frist row and not all the data with this code:

    Private Sub DISPLAY()
        conn.Close()
        Dim cmd2 As New MySqlCommand
        Dim myDA2 As New MySqlDataAdapter(cmd2)
        Dim myDT2 As New DataTable
        cmd2.Connection = conn
        cmd2.CommandText = "SELECT * FROM esd_reco where IDNumber = '" & txtG.Text & "' ORDER BY Date DESC"
        myDA2.Fill(myDT2)
        dghistory.DataSource = myDT2
        conn.Close()

Open in new window


txtG.text is the ID Number
Help please, thank you.
Rowel VirgoVisual Studio .NETAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
are you using MySQL as your database? If yes then Top 1 will not worked for you.

you can try use LIMIT clause:

SELECT * FROM msd.esd_reco where IDNumber = '1504579' ORDER BY Date DESC LIMIT 1

Open in new window


or if you selecting the latest record for each grouping, try:

SELECT a.* FROM msd.esd_reco a
inner join 
(
	select IDNumber, max(date) date from msd.esd_reco group by IDNumber
) b
on a.IDNumber = b.IDNumber and a.date = b.date

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try this -

cmd2.CommandText = "SELECT TOP 1 * FROM esd_reco where IDNumber = '" & txtG.Text & "' ORDER BY Date DESC"
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Or this -

cmd2.CommandText = "SELECT Name,Department,User_Type,MAX(date) date,MAX(IdNumber)IdNumber FROM esd_reco where IDNumber = '" & txtG.Text & "' ORDER BY Date DESC" & "GROUP BY Name,Department,User_Type"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.