6 Replies - 3047 Views - Last Post: 23 April 2010 - 02:34 PM Rate Topic: -----

#1 vennesschan  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

Get Last 20 rows data from sql db and sort by date

Posted 23 April 2010 - 03:24 AM

Hi, I am trying to get the last 20 rows data from the sql db to the ListView from individual Name which i input to the txtbox. However it doesn't work out on the following way. Can someone tell me what did i do wrong. Thank you.

 
            Dim ID As String
            ID = Inputtxt.Text

            If ID = "" Then
                MsgBox("Please enter a Name")
            End If
           
            Dim SelectCmd As String
            SelectCmd = "Select Distinct Top 20 Name, Date, High, Low from Report where Name='  Order By Date ASC" & ID & "'"



Is This A Good Question/Topic? 0
  • +

Replies To: Get Last 20 rows data from sql db and sort by date

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Get Last 20 rows data from sql db and sort by date

Posted 23 April 2010 - 03:54 AM

Using the same concept you did in your prior post you would just specify And Name = 'somename'

SelectCmd = "Select Id, Date, High, Low from Report WHERE Name = '" & ID & "' AND Date BETWEEN '" & startDate & "' AND '" & endDate & "' ORDER BY Date"

Was This Post Helpful? 0
  • +
  • -

#3 vennesschan  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

Re: Get Last 20 rows data from sql db and sort by date

Posted 23 April 2010 - 05:15 AM

you are the best, thank you Charlie :)
Was This Post Helpful? 0
  • +
  • -

#4 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Get Last 20 rows data from sql db and sort by date

Posted 23 April 2010 - 07:53 AM

The way ChalieMay showed you will not get you the last 20 rows, it will only get you what is in the WHERE clause, to get the last 20 rows, you can still use the WHERE clause but you add TOP 20 and ORDER descending on what ever column you want.

eg.

SELECT TOP 20 Surname FROM Individuals ORDER BY Surname DESC



If it's the Surname column, the result will be in alphabetical order but in reverse, so sort it again, you can use the following

eg.

WITH Results AS (
SELECT TOP 20 Surname FROM Individuals ORDER BY Surname DESC
) SELECT * FROM Results ORDER BY Surname



You fill a Temp Table called "Results" then querry the Temp Table and sort it again...
Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Get Last 20 rows data from sql db and sort by date

Posted 23 April 2010 - 08:30 AM

Motcom, He's actually wanting only data from the last 20 days (See Previous Topic) Now he's just wanting to know how to only pull the last 20 days for a given name and sort it.
Was This Post Helpful? 0
  • +
  • -

#6 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Get Last 20 rows data from sql db and sort by date

Posted 23 April 2010 - 11:17 AM

Haven't seen the last topic, been in angola for 2 weeks of fishing... sorry.
Was This Post Helpful? 0
  • +
  • -

#7 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Get Last 20 rows data from sql db and sort by date

Posted 23 April 2010 - 02:34 PM

Hope you didn't take that the wrong way, I was just pointed it out because it wasn't as clear in this current topic.
What kind of fish were you catching? Can't really tell in the picture on your profile. Actually after zooming in, it looks like you just sitting in the current drinking a beer :P

This post has been edited by CharlieMay: 23 April 2010 - 02:38 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1