SQL Server TSQL equivalent to 'MySQL Limit'?

Question

I know MySQL has limit command, but I don't know what command should I use in SQL server 2008?

I need select clientId and last day of each pickup, most of the clients have multiple pickups (65 000 + records in pickup table).

select  P.ClientID,LastName+' '+FirsName as Name , Adress,p.PickupDate
from Pickup P,Clients C
where P.ClientID= C.ClientID
order by PickupDate desc limit 1


throwing error *Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'limit'.*
because **LIMIT** is not key word in SQL server

This was already answered here >> http://stackoverflow.com/questions/9013177/mysql-limit-clause-equivalent-for-sql-server — mockaroodev, Feb 25 '13 at 04:23

mson · Accepted Answer · 2013-02-25T21:12:25.687

3

the limit command is top in sql server

select top 1 P.ClientID
   ,LastName+' '+FirsName as Name 
   ,Adress
   ,p.PickupDate
from Pickup P
join Clients C on P.ClientID= C.ClientID
order by PickupDate desc

per poster's clarification - this isn't even a top/limit question. this is a simple group by statement.

select P.ClientID
   ,LastName+' '+FirsName as Name 
   ,Adress
   ,max(p.PickupDate)
from Pickup P
join Clients C on P.ClientID= C.ClientID
group by p.clientid, lastname + ' ' + firsname,adress

edited Feb 25 '13 at 21:12

answered Feb 25 '13 at 04:50

mson

7,762
6
40
70

it is not right because its return only last record but i need return last record for each clientID in table – Andrey Feb 25 '13 at 08:12
2

lol - this is what you specified in requirement above. it can be modified for what you intended (which i'll edit into answer). – mson Feb 25 '13 at 21:10
no way! it was that simple? really? i spend almost day to do it. and it just simple join with MAX function. Thank YOU !! @mson – Andrey Feb 25 '13 at 22:59
2

i think it's funny that 4 dudes didn't even read the problem and tried to close your question down. – mson Feb 26 '13 at 04:33

score 1 · Answer 2 · answered Feb 25 '13 at 04:26

1

This is normally done (pre-2012) using windowing functions:

;WITH Data
AS
    (
    SELECT P.ClientID
        , LastName + ' ' + FirsName as Name
        , Address
        , PickupDate
        , ROW_NUMBER() OVER(PARTITION BY P.ClientID ORDER BY PickupDate DESC)
            AS [Entry #]
    FROM Pickup P
    JOIN Clients C
        ON P.ClientID= C.ClientID
    )
SELECT *
FROM Data
WHERE [Entry #] = 1
ORDER BY PickupDate DESC

See this blog post for some good enhancements to the technique (to avoid scanning unnecessary rows).

answered Feb 25 '13 at 04:26

Sean Vieira

155,703
32
311
293

Msg 4108, Level 15, State 1, Line 9 Windowed functions can only appear in the SELECT or ORDER BY clauses. – Andrey Feb 25 '13 at 04:28
@AndreyIvanov - apologies, forgot the CTE. – Sean Vieira Feb 25 '13 at 04:29
Thanx it works ! but where I can find something which can helps me understand your query? as I sad it works but I want to know how :) – Andrey Feb 25 '13 at 04:33
Is there a way to make it simpler? because I need wright procedure based on this query which will change ststusId of client if he/she did not do pickup more than 2 months. – Andrey Feb 25 '13 at 08:14

Meherzad · Answer 3 · 2013-02-25T04:53:44.977

1

Try this

select P.ClientID, LastName+' '+FirsName as 'Name', Adress, max(p.PickupDate)
from Pickup P,Clients C
where P.ClientID= C.ClientID
GROUP BY P.ClientID, Adress, LastName+' '+FirsName
order by PickupDate desc

Fiddle

edited Feb 25 '13 at 04:53

answered Feb 25 '13 at 04:27

Meherzad

8,433
1
30
40

I need only last PickupDate for each ClientD,your query will list all of the records for each clientID. It is my original query but without limit 1 :) – Andrey Feb 25 '13 at 04:35
Check the edit.. Hope it helps – Meherzad Feb 25 '13 at 04:44

SQL Server TSQL equivalent to 'MySQL Limit'?

3 Answers3