SQL statement to select unique

Question

I have the following data in table called Lockers (there are other columns but lets ignore them now).

---------------------------------------
| ID    | LockerNo    | BookingID      |
----------------------------------------
| 1     | A01         | 111111         |
| 1     | A01         | 222222         |
| 1     | A01         | 333333         |
| 2     | A02         | 888888         |
| 2     | A02         | 999999         |
| 3     | A03         | 121212         |
| 3     | A03         | 232323         |
| 4     | A04         | 777777         |
| 5     | A05         | 555555         |
| 6     | A06         | 444444         |
----------------------------------------

The above output generate from the following SQL:

Select LockerID, LockerNo, LBD.BookingID 
From Lockers L 
Inner Join LockerBookingDetails LBD On LBD.LockerID = L.ID  
Where Zone = 1 And IsActive = 1 
Order By L.LockerNo, LBD.BookingID

I've been struggling to create a SQL statement to display the following output. The condition is to get the latest data (assuming that BookingID is sorted ascendingly).

---------------------------------------
| ID    | LockerNo    | BookingID      |
----------------------------------------
| 1     | A01         | 333333         |
| 2     | A02         | 999999         |
| 3     | A03         | 232323         |
| 4     | A04         | 777777         |
| 5     | A05         | 555555         |
| 6     | A06         | 444444         |
----------------------------------------

Dan D · Answer 1 · 2019-07-19T03:58:39.800

One alternative would be using window functions, this would give you the greatest BookingId per LockerNO:

SELECT
    Id, 
    LockerNo,
    BookingId
FROM
(
    SELECT
        ROWID = ROW_NUMBER() OVER(PARTITION BY LockerNO ORDER BY lbd.BookingId DESC),
        l.Id,
        l.LockerNo,
        lbd.BookingId
    FROM
        Lockers l 
        inner join LockerBookingDetails lbd on lbd.LockerID = l.ID  
    Where 
        Zone = 1 
        And IsActive = 1
) r
WHERE
    r.ROWID = 1

Understanding the OVER clause and ranking functions will help you out, see the SQL Server documentation:

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

Thankss.. it worked now.. I use GROUP BY .. But this OVER clause is new to me. maybe I can explore more.. thanks a lot :) — Julie, Jul 19 '19 at 04:03

score 2 · Accepted Answer · edited Jul 19 '19 at 04:00

2

SELECT LockerID, LockerNo, MAX(BookingID) BookingID 
FROM 
(
   SELECT LockerID, LockerNo, LBD.BookingID 
   FROM   Lockers L INNER JOIN LockerBookingDetails LBD ON LBD.LockerID = L.ID  
   WHERE  Zone     = 1 
   AND    IsActive = 1 
) A 
GROUP BY LockerID, LockerNo
ORDER BY LockerID, LockerNo

edited Jul 19 '19 at 04:00

Jeroen Heier

3,520
15
31
32

answered Jul 19 '19 at 03:50

Samat Kurmanov

42
2

Its work now.. owh.. so i need to group it.. now i get .. Thanks a lot :) – Julie Jul 19 '19 at 04:02

score 1 · Answer 3 · answered Jul 19 '19 at 12:05

Just add aggregation to your query:

Select L.LockerID, L.LockerNo, MAX(LBD.BookingID) as BookingID
From Lockers L Inner Join
     LockerBookingDetails LBD
     On LBD.LockerID = L.ID  
Where Zone = 1 And IsActive = 1 
Group by L.LockerID, L.LockerNo
Order By L.LockerNo, MAX(LBD.BookingID);

No subquery is necessary.

SQL statement to select unique

3 Answers3