SQL Server return multiple queries in a single query

Question

I have the following table

ID      TYPE        Text       ImagePath     Title    Date

 1      Text        Test        NULL          NULL     14/10/2013
 2      Image       NULL        /test/test    NULL     14/10/2013
 3      Title       NULL        NULL          Test     14/10/2013
 4      Text        Test2       NULL          NULL     20/12/2012

How can I retrieve ONLY the last 3 (sorted by date) records, 1 with type Text, 1 with type Image and 1 with type Title only? (Basically I only need the type and related field which is not NULL.

Are you looking for only the most recent record of each type? — nycdan
– nycdan, Commented Oct 14, 2013 at 15:25
@nycdan yes that is exactly what im looking for. Sorry for not being in title/description but did not know exactly how to word it. — rikket
– rikket, Commented Oct 14, 2013 at 15:27
When you have a column "type" and the 3 columns all are varchar, why don't you combine them in one column? — fancyPants
– fancyPants, Commented Oct 14, 2013 at 15:28
@fancyPants yes that actually makes more sense and would solve my problem. Thank you — rikket
– rikket, Commented Oct 14, 2013 at 15:37

fancyPants · Accepted Answer · 2013-10-14 15:39:21Z

1

Apart from restructuring your database as I said in the comment on your question, this should give you what you want (if I understood the question properly):

SELECT
TOP 1 
'Text' AS Type,
Text
FROM
your_table
ORDER BY Date DESC
UNION ALL
SELECT
TOP 1 
'ImagePath',
ImagePath
FROM
your_table
ORDER BY Date DESC
UNION ALL
SELECT
'Title', 
TOP 1 Title
FROM
your_table
ORDER BY Date DESC

You may have to put some parantheses here and there to make it work...

answered Oct 14, 2013 at 15:39

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VancleiP · Accepted Answer · 2013-10-14 15:27:21Z

0

One idea could be:

SELECT  * FROM table where TYPE = 'Text' OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY 
union
SELECT  * FROM table where TYPE = 'Image' OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY 
union
SELECT  * FROM table where TYPE = 'Title' OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

answered Oct 14, 2013 at 15:27

VancleiP

6574 silver badges7 bronze badges

6 Comments

Charleh Over a year ago

Use UNION ALL when you don't need to eliminate duplicates - is this even compatible with MS Sql Server

VancleiP Over a year ago

Yes, we could use union all here, but there's no need because it won't return duplicate rows as long as the type betweens the select are always different ;-)

nycdan Over a year ago

does this order properly to select the most recent? Also, I agree with Charleh that this isn't SQL Server syntax.

fancyPants Over a year ago

You misunderstood him. Using UNION ALL is a performance tweak, as the Server doesn't need to look for duplicates. Also your answer is totally wrong. OP asked for "last 3".

Charleh Over a year ago

Yes - avoid UNION because it's a performance penalty - especially for larger data sets when you don't need to eliminate dupes. My rule of thumb is UNION ALL unless you know why you are using UNION.

|

Collectives™ on Stack Overflow

SQL Server return multiple queries in a single query

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related