Select Max and Select other column

Question

I am trying to get the max + 1 value from one column, and all of the values from another column. However, my query does not give any results.

For example,

 SectionItemID   SectionItem
 1                    blue
 2                    red

The query should return

SectionItemID    SectionItem
3                    blue
                     red

Heres what I have

SELECT SectionItem,MAX(SectionItemID) + 1 AS SectionItemID FROM Core.SectionItem_Lkup

`my query is not correct.` I believe you should define what exactly is wrong with your query. — default locale, Jul 23 '14 at 18:01
possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Strawberry, Jul 23 '14 at 18:15

score 4 · Accepted Answer · answered Jul 23 '14 at 18:04

4

SELECT SectionItem,
       (select MAX(SectionItemID)+1 FROM Core.SectionItem_Lkup) AS SectionItemID
FROM Core.SectionItem_Lkup

answered Jul 23 '14 at 18:04

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Jul 23 '14 at 18:11

Whenever you GROUP BY, you should aggregate the other columns involved.

Mysql does allow to omit aggregation on other colums
MsSQL does not, cause the result is undefined for columns without Aggregation.

Best way is to aggregate other columns. For your szenario, you could use group_concat

  SELECT MAX(SectionItemID)+1, Group_concat(SectionItem) FROM tbl

Note: The query does not contain any Group By, because you dont want to group on SectionItemId nor SectionItem. Omiting the Group By and using aggregate-functions will use them on the whole table.

Output:

MAX(SECTIONITEMID)+1    GROUP_CONCAT(SECTIONITEM)
3                     blue,red

http://sqlfiddle.com/#!2/353bf3/6

score 0 · Answer 3 · answered Jul 23 '14 at 18:14

0

select case when t2.SectionItem = 'blue' 
    then cast(max(t1.SectionItemID) + 1 as varchar(1))
    else '' end 
as SectionItemID
, t2.SectionItem
from Core.SectionItem_Lkup t1
full outer join Core.SectionItem_Lkup t2 on 1 = 1
group by t2.SectionItem
order by 
case when t2.SectionItem = 'blue' 
    then cast(max(t1.SectionItemID) + 1 as varchar(1))
    else '' end 
desc

answered Jul 23 '14 at 18:14

jediCouncilor

798
5
9

This will return exactly the output you've shown here. – jediCouncilor Jul 23 '14 at 18:15

Select Max and Select other column

3 Answers3