Sql read specific row value

Question

I need to use a single query to get the first 5 values as a single row of results. I am aware that the following does not work but I think it gives a good example of what I am trying to achieve

SELECT
   idimage[0] AS Row1, //idimage row 1 value
   idimage[1] AS Row2, //idimage row 2 value
   idimage[2] AS Row3, //idimage row 3 value
   idimage[3] AS Row4, //idimage row 4 value
   idimage[4] AS Row5  //idimage row 5 value
FROM
IMAGES

Does anyone know the best way to achieve this. I am sure the syntax must be pretty simple but i cant seem to find it.

Please tag with the database you are using. – Gordon Linoff Jan 07 '19 at 23:57 — Gordon Linoff, Jan 07 '19 at 23:57
Sql-Server (Microsoft) – Christopher Vickers Jan 07 '19 at 23:58 — Christopher Vickers, Jan 07 '19 at 23:58

score 1 · Answer 1 · answered Jan 07 '19 at 23:58

You can use conditional aggregation:

select max(case when seqnum = 1 then idimage end) as idimage1,
       max(case when seqnum = 2 then idimage end) as idimage2,
       max(case when seqnum = 3 then idimage end) as idimage3,
       max(case when seqnum = 4 then idimage end) as idimage4,
       max(case when seqnum = 5 then idimage end) as idimage5       
from (select i.*, row_number() over (order by ?) as seqnm
      from images i
     ) i;

Note that SQL tables represent unordered sets. The ? in the order by is for the column that specifies the ordering.

Ass3mbler · Answer 2 · 2019-01-08T00:12:11.710

0

If I understand correctly what you want to do, maybe you can use some subquery this way:

SELECT 
 (SELECT idimage from IMAGES ORDER BY id desc OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY ) AS ROW1, 
 (SELECT idimage from IMAGES ORDER BY id desc OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS ROW2, 
 (SELECT idimage from IMAGES ORDER BY id desc OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS ROW3, 
 (SELECT idimage from IMAGES ORDER BY id desc OFFSET 3 ROWS FETCH NEXT 1 ROWS ONLY) AS ROW4, 
 (SELECT idimage from IMAGES ORDER BY id desc OFFSET 4 ROWS FETCH NEXT 1 ROWS ONLY) AS ROW5
FROM IMAGES;

edited Jan 08 '19 at 00:12

answered Jan 07 '19 at 23:59

Ass3mbler

3,855
2
20
18

Thank you but this assumes i know the top 5 id's, which may change if the older records have been removed – Christopher Vickers Jan 08 '19 at 00:03
Ok, I didn't understand that, I'll change the query then. Do you want to be ordered by id descending? – Ass3mbler Jan 08 '19 at 00:04

score 0 · Answer 3 · answered Jan 08 '19 at 00:01

0

use LIMIT and ORDER BY to restrict your rows to 5. Check for instance How to concatenate text from multiple rows into a single text string in SQL server? to concatanate the resulting rows

answered Jan 08 '19 at 00:01

lpp

26
1

Sql read specific row value

3 Answers3