CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate-functions
ʞᴉɯ
ʞᴉɯ

Reputation: 5594

Tsql to get first random product in a category

I've this result set:

select a.id, a.categoria from Articolo a
where novita = 1
order by a.categoria, newid()

id          categoria
----------- -----------
3           4
11          4
1           4
12          5
13          5
4           6

and i would to get the first product (in a random order) from each different category:

id          categoria
----------- -----------
3           4
12          5
4           6

Ideally something like 

select FIRST(a.id), a.categoria from Articolo a
where novita = 1
order by a.categoria, newid()

Any ideas?

Upvotes: 0

Views: 109

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Use MAX(a.id) with GROUP BY a.categoria

SELECT MAX(a.id), a.categoria 
from Articolo a
where novita = 1
GROUP BY a.category

Update

To get random id for each categoria you can use the ranking function ROW_NUMBER() OVER(PARTITION BY categoria) with ORDER BY NEWID to get a random ordering, like this:

WITH CTE
AS
(
  SELECT id, categoria, ROW_NUMBER() OVER(PARTITION BY categoria
                                          ORDER BY NEwID()) AS rn
  FROM Articolo
)
SELECT id, categoria
FROM CTE
WHERE rn = 1;

See it in action here:

This way, it will give you a random id for each categoria each time.

However, If you want the first, you can use the ORDER BY(SELECT 1) inside the ranking function ROW_NUMBER():

WITH CTE
AS
(
  SELECT id, categoria, ROW_NUMBER() OVER(PARTITION BY categoria
                                          ORDER BY (select 1)) AS rn
  FROM Articolo
)
SELECT id, categoria
FROM CTE
WHERE rn = 1;

This will give you the first id for each categoria.

Note that: There is no meaning of the first value in the database concepts, because in the relational model, the rows order is not significant. And it is not guaranteed to return the same order each time, you have to ORDER BY specific column to get consistent ordering.

Upvotes: 1

Related Questions