CODEWITHSUNDEEP
sql
Bash
Bash

Reputation: 4420

SQL query to prioritize a certain column value

I have a table EMP which looks something like this:

Name  DeptID  
A     1000

B     1000

C     1000

D     3000

E     6000

The table has thousands of records, with unique names and DeptIDs 1000,3000,6000.

I need to fetch 2000 rows from the table, which I am currently doing using the following query

select * from EMP where rownum<2001;

Now, i have a requirement which says that records with DeptID=1000 need to be prioritized, such that query returns maximum possible records with DeptID=1000. So, if number of records with DeptID=1000 are >2000, then return only DeptID=1000 records in query response. And, if number of records with DeptID=1000 are <2000, lets say 500, then query must ensure that all such 500 records are returned, and other 1500 can be the ones where ID!=100

Upvotes: 0

Views: 2757

Answers (3)

Alan Mills
Alan Mills

Reputation: 1

From your description, I think that you need to select the top 2000 records from the EMP table, selecting the DeptID=1000 records first.

This approach creates a results table that will hold a maximum of 4000 records with the first 2000 containing DeptID=1000 records (if there are 2000 or more).

IF OBJECT_ID ('EMPResults', 'U') IS NOT NULL
DROP TABLE EMPResults;
GO

(SELECT TOP 2000 * INTO EMPResults FROM EMP WHERE DeptID = 1000)
UNION
(SELECT TOP 2000 * FROM EMP WHERE DeptID <> 1000);
GO

SELECT TOP 2000 *
FROM EMPResults;
GO

Upvotes: 0

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

DISCLAIMER: You didn't specify what RDMS you use, so I wrote this in T-SQL for MS SQL Server.

I went with this idea because if it's required that you happen to have a particular department id that is more important, I can see a situation where after you get all the records for that department you should go to another, then another, in some arbitrary order. This well let you do that:

DECLARE @DeptOrdering TABLE
(
    DeptId INT,
    SortOrder INT
)

INSERT @DeptOrdering (DeptId, SortOrder)
VALUES
    (1000, 10)
    , (6000, 20)
    , (3000, 30)

SELECT TOP 2000
    E.*
FROM EMP AS E
INNER JOIN @DeptOrdering Ordering
    ON Ordering.DeptId = E.DeptId
ORDER BY Ordering.SortOrder --, Other columns

Upvotes: 1

Kyra
Kyra

Reputation: 5407

Select top 2000 * 
from EMP 
Order by DeptID

If you are using mysql rather than tsql then instead of the top you would use limit at the end.

Upvotes: 1

Related Questions