Menu
This is a product table and have few million of records.
I want to list record as below:
Normally I use:
SELECT id,
product_name,
store_id
FROM product
GROUP BY store_id
ORDER BY id.
Currently having SQL performance issue. I need SQL query to output result like this.
439
To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ). You assign the row numbers within each group (i.e., year).
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
You can use row_number() to get the row number of the row. It uses the over command - the partition by clause specifies when to restart the numbering and the order by selects what to order the row number on.
There are many alternatives to solves this, one which I recommend is to have joined a subquery which separately gets the latest ID (assuming that the column is AUTO_INCREMENTed) for each store_ID.
SELECT a.*
FROM tableName a
INNER JOIN
(
SELECT store_ID, MAX(ID) max_ID
FROM tableName
GROUP BY store_ID
) b ON a.store_ID = b.store_ID AND
a.ID = b.max_ID
for better performance, be sure to have an index on these columns: ID and store_id.
UPDATE 1
if you want to have limit for every records, use this below,
SELECT ID, product_Name, store_ID
FROM tableName a
WHERE
(
SELECT COUNT(*)
FROM tableName b
WHERE b.store_ID = a.store_ID AND b.ID >= a.ID
) <= 2;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
