CODEWITHSUNDEEP
sqlsql-server
1Mayur
1Mayur

Reputation: 3485

Query join and optimization

I have a table INFO whose design is like this

id - bigint
Name - varchar2
refid - bigint
status - int
ExpDate - datetime

I want to write a query where all records should come from this table with status 0, 1, 2, 3, but only 7 latest records by expdate should come whose status is 4.

I can't figure out how can we achieve this in T-SQL.

Please help me out or give me suggestion so that I can start writing it.

Upto now I have written

SELECT * 
FROM INFO 
WHERE STATUS IN (0,1,2,3) AND 
      REFID IN (SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW')

SELECT TOP 7
FROM INFO 
WHERE STATUS=4 AND 
      REFID IN(SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW') 
ORDER BY EXPDATE DESC

i need to join them??? how.. suggest and also the query

(SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW')

is coming twice how to optimize it thanks..

Upvotes: 0

Views: 100

Answers (4)

devarc
devarc

Reputation: 1157

gbn and Pavanred answers about UNION solve your problem "how to join this results". If it comes to optimization you can create temp table for results from select. Then query executes only once.

But... I think query is too trivial to optimize this in that way.

Anyway:

SELECT REFID 
INTO #temp1 
FROM REFTABLE WHERE REFCHAIN='BMW'

And then use this in both queries like that:

WHERE REFID IN (SELECT REFID FROM #temp1)

Or like that

SELECT ...
FROM INFO inf
INNER JOIN #temp1 t ON inf.REFID = t.REFID
...

Upvotes: 0

Teju MB
Teju MB

Reputation: 1383

for latest 7 values in ur reftable u can use this query--

select * from REFTABLE where REFID not in( select top (select count(*)-7 from info) REFID from REFTABLE )

it would have better if u could post the table structure..

Upvotes: 0

pavanred
pavanred

Reputation: 13803

Do you want to optimize this or do you want to just remove the duplicate records.

If you want to remove duplicate records then you can try using UNION- 

SELECT * FROM INFO 
INNER JOIN REFTABLE ON INFO.refId = REFTABLE.Id
WHERE REFCHAIN = 'BMW' AND INFO.status IN (0,1,2,3)
UNION
SELECT FROM 
    (
       SELECT TOP 7 FROM INFO 
       INNER JOIN REFTABLE ON INFO.refId = REFTABLE.Id
       WHERE INFO.status=4 AND REFCHAIN='BMW'
       ORDER BY EXPDATE DESC
    ) T

Upvotes: 1

gbn
gbn

Reputation: 432261

Just UNION your 2 current queries together (you need a derived table to get the TOP though)

SELECT ...
FROM INFO 
WHERE STATUS IN (0,1,2,3) AND 
      REFID IN (SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW')
UNION ALL
SELECT ...
FROM 
    (
    SELECT TOP 7 ...
    FROM INFO 
    WHERE STATUS=4 AND 
          REFID IN(SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW') 
    ORDER BY EXPDATE DESC
    ) T

You can get fancier, but it may not be as efficient:

SELECT *
FROM
    (
    SELECT ...,
       ROW_NUMBER() OVER (ORDER BY EXPDATE DESC) AS rn
    FROM INFO 
    WHERE REFID IN (SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW')
    ) T
WHERE
    rn <= 7 OR STATUS <= 4

Upvotes: 4

Related Questions