CODEWITHSUNDEEP
sqllinq
TheBear
TheBear

Reputation: 855

SQL Select statement help: use row if value exists else use another row

This is my table structure:

DATE (date)
SOURCE (varchar(50))
COMPANY (varchar(50))
PERSON (varchar(50))

I then have say 4 rows of data:

2015-11-22 , "AUTO", "COMPANY1", "PERSONAUTO"
2015-12-05 , "AUTO", "COMPANY2", "PERSON2"
2015-11-22 , "CUSTOM", "COMPANY1", "PERSONCUSTOM"
2015-11-23 , "AUTO", "COMPANY3", "PERSON3"

I want to create a sql query that selects rows only with the Date within November 2015, and also to prioritize the source of "CUSTOM" over "AUTO" if it exists for the company, otherwise use the "AUTO" one. So the desired output would be:

2015-11-22 , "CUSTOM", "COMPANY1", "PERSONCUSTOM"
2015-11-23 , "AUTO", "COMPANY3", "PERSON3"

If this method is too difficult to do it all in sql, I could grab all the data into c# DataTable and use linq. If someone could provide an example for that instead, that would be great! (Not sure which is more efficient...)

Upvotes: 0

Views: 136

Answers (2)

VishalProgrammer
VishalProgrammer

Reputation: 44

Maybe you can use following query

Select * 
From TABLE_NAME
Where DATE <= '30-11-2015' 
Order By COMPANY, SOURCE = 'CUSTOM' DESC

Upvotes: 0

mikeagg
mikeagg

Reputation: 751

You want just one row per company? Then partition the results by Company, order by Source, then select only the top row in each partition.

SELECT Date, Source, Company, Person,
    ROW_NUMBER() OVER (PARTITION BY Company 
        ORDER BY Source DESC) AS rk
FROM MyTable
WHERE MONTH(Date) = 11 AND YEAR(Date) = 2015
    AND rk = 1

I should add that if your table is large, you'll probably want to index on the date field. In this case the MONTH() and YEAR() functions are not the most efficient way of filtering by date. Better to use something like:

WHERE Date >= @StartDate AND Date < DATEADD(month, 1, @StartDate)

Upvotes: 1

Related Questions