Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    Display last 5 records Query

    I have a query with a date and want to display last 5 records, how do you do it. Think about 1 table with one field - date. How do you display last 5...? THANK YOU

  2. #2
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    Try this:

    SELECT TOP 5 Dates FROM tblDates ORDER BY Dates DESC;

    Seems to work.

  3. #3
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    Thanks, that definitely works, let me take this further. I would like to select SELECT TOP n number. I'm bring the n from another form and it doesn't work. Is there any reason why after top must be a hard coded integer and not a variable. Anybody knows a way around it?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the generic "top n" syntax which involves a correlated subquery, and then you can prompt for the number

    Code:
    select foo
         , bar
      from yourtable zz
     where [enter "top" number] 
         > ( select count(*)
               from yourtable
              where bar > zz.bar )
    in english: select a row only if the "top" number is greater than the number of rows that have a higher value than this row

    stated differently: select a row if the number of rows that have a higher value than this row is less than the top number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    I do appreciate the input, it doesn't work though since the selection is in hunderds insted of last n record for the date. This is the code I put in, hopefully someone can help

    SELECT [ITEM KEY], DATE
    FROM [TBL:BATCHES] zz
    WHERE [enter "top" number] > (select count(*) from [TBL:BATCHES] where DATE > zz.DATE)


    Just to clarify. the code bellow worked great but the problem is that I want to have the integer 5 replaced with variable that asks the user how many records to display instead of solid 5 every time

    SELECT TOP 5 [TBL:BATCHES].[ITEM KEY], [TBL:BATCHES].DATE
    FROM [TBL:BATCHES]
    WHERE ((([TBL:BATCHES].[ITEM KEY])="1240-121"))
    ORDER BY [TBL:BATCHES].DATE DESC;

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739
    make a text box "topHowMany" and a button "butGo" on a form.

    Code:
    Private sub butGo_Click()
    dim strSQL as string
    strSQL = "SELECT TOP " & topHowMany & " [TBL:BATCHES].[ITEM KEY], [TBL:BATCHES].DATE FROM [TBL:BATCHES] "
    strSQL = strSQL & "WHERE ((([TBL:BATCHES].[ITEM KEY])= '1240-121')) ORDER BY [TBL:BATCHES].DATE DESC;"
    strSQL now does what you want.
    you could then...
    save it into a querydef
    or set the .recordsource of a form to it
    or set the .rowsource of a combo or list to it

    whilst you are at it, you can also get 1240-121 from a text box. it is slightly different because this second one is text:
    Code:
    strSQL = strSQL & "WHERE ((([TBL:BATCHES].[ITEM KEY])= '" & anotherBox & "')) ORDER BY [TBL:BATCHES].DATE DESC;"
    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    I bet this is going to work, I have never saved to querydef, neither I know what it means. The way it was set up, I had the SQL written behind a report. Now I just can't figure it how to use it that way. Thanks all of you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •