CODEWITHSUNDEEP
c#sql
Tanya
Tanya

Reputation: 1621

Get the Rows separately based on the ID in sql

I have a table as follows,

TypeID   Name         Date
-------------------------------
1        Carrot    1-1-2013
1        Beetroot  1-1-2013
1        Beans     1-1-2013
2        cabbage   1-1-2013
2        potato    1-1-2013
2        tomato    1-1-2013
2        onion     1-1-2013

If need 2 rows then it should return 2 rows from TypeId 1 and 2 rows from TypeId 2.If need the only 4 rows, means I have to get 4 rows from TypeId 1 and 4 rows from TypeId 2 but TypeId 1 has only 3 rows so we need to get only 3 rows for typeId 1

How to do that? Shall I add RowNumber?

Upvotes: 3

Views: 101

Answers (4)

Tanya
Tanya

Reputation: 1621

;With CTE(TypeID,Name,Date,RowNo)
AS
(
select *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)  from TableVEG
)
Select Top(@noofRows*2) * from CTE where RowNo<=@noofRows order by rowno

The above query worked.. Thank u all... :-)

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

For SQL Server;

EDIT: Your question changed slightly;

If you want want a maximum of x items per category, you can use ROW_NUMBER();

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY TypeID ORDER BY Name) rn FROM Table1
)
SELECT TypeID, Name, [Date] FROM cte 
WHERE rn <=3       -- here is where your x goes
ORDER BY TypeID;

An SQLfiddle to test with.

Upvotes: 2

Reed Copsey
Reed Copsey

Reputation: 564631

You can write your query to order by the TypeID.

Then, if you're using SQL, you could use SELECT TOP N or LIMIT N (depending on the DB), or with TSQL and SQL Server, use TOP(N) to take the top N rows.

If you're using a LINQ based ORM from your C# code, then you can use Take(N), which automatically creates the appropriate query based on the provider details to limit the number of results.

Upvotes: 1

JMG
JMG

Reputation: 163

I think you should use a query to select your 3 rows from type 1.....and then the however many rows from type 2 and then add the results together.

Upvotes: 0

Related Questions