CODEWITHSUNDEEP
mysqlsql
Sylver
Sylver

Reputation: 8967

Figure out the last item of a group of items in SQL

Imagine a MySql table like this:

---------------------------
ListID | itemID | Item
---------------------------
List_1 | item_1 | Apple
---------------------------
List_1 | item_2 | Orange
---------------------------
List_1 | item_3 | Pear
---------------------------
List_2 | item_1 | Potatoes
---------------------------
List_2 | item_2 | ...

I have a getNextItem() function which increments the itemID and use a SQL query to get the next item:

SELECT * FROM items_tbl
WHERE listID = "$listId"
AND itemID = "$itemID"
LIMIT 1

Is there an easy way in SQL to figure out if the item I am getting is the last one of that list?

I could add a column to the table and mark the last item of each list, or I could make another query, get the highest item number and compare with my list the current item, or get the total number of items in the list and keep track of the item number in relation to the list's total... but all these solutions feel like a hack.

Can someone suggest something better in SQL and explain a bit how it works?

Upvotes: 2

Views: 156

Answers (3)

user330315
user330315

Reputation:

You did not state your DBMS, but the following is an ANSI compliant SQL (should work on PosgreSQL, Oracle, DB2)

SELECT *
FROM (
    SELECT listid, 
           itemid,
           case 
              when lead(itemid) over (partition by listid order by itemid) is null then 'last'
              else 'not_last'
           end as last_flag
    FROM items_tbl
    WHERE listID = 'List_1'
) t
WHERE itemID = 'item_2'

Edit, the following should work on SQL Server (as that doesn't yet support lead()):

SELECT listid, 
       itemid,
       case 
         when rn = list_count the 'last'
         else 'not_last'
       end
FROM (
    SELECT listid, 
           itemid,
           row_number() over (partition by listid order by itemid) as rn,
           count(*) over (partition by listid) as list_count
    FROM items_tbl
    WHERE listID = 'List_1'
) t
WHERE itemID = 'item_2'

Upvotes: 0

Jan Vorcak
Jan Vorcak

Reputation: 20009

I'd add an autoincrement primary key named id to your table and use nested SQL query to get maximal id of the table

SELECT id, ListID, itemID, item, (SELECT MAX(id) FROM table_name) as max_id from table_name;

this will return all columns of your table with one additional column named max_id with the maximal id, so you can check in your app whether max_id == id, if so it's the last column

Upvotes: 1

Kevin
Kevin

Reputation: 56099

The easiest thing to do would probably be to, before you Start, get the highest id in each list:

Select listID, max(itemID) from mytable group by listID

Upvotes: 0

Related Questions