CODEWITHSUNDEEP
mysqlsqlsql-servert-sql
DalW
DalW

Reputation: 59

Group By Largest revision string

I need to figure out a way to group these properly. I have a results set that returns Incident numbers which are actually datatype string, that have revisions done. I only need the grouping of the last revision number entered. Such as the below. 

INC-101
INC-101
INC-101A1
INC-101A1
INC-101A2
INC-101A2
JNC-101
JNC-101
JNC-101S1
JNC-101S1

How could I only see the highest revision number such as the following:

INC-101A2
INC-101A2
JNC-101S1
JNC-101S1

Upvotes: 1

Views: 58

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30819

You can use GROUP BY with SUBSTRING, e.g.:

SELECT MAX(incident_no)
FROM incidents
GROUP BY SUBSTRING(incident_no, 1, 7);

Here's the SQL Fiddle.

Update

If you want all the rows belonging to the latest revision number then you can use the above query as sub-query, e.g.:

SELECT *
FROM incidents
WHERE incident_no IN (
    SELECT MAX(incident_no)
    FROM incidents
    GROUP BY SUBSTRING(incident_no, 1, 7)
);

Upvotes: 2

Stephan Lechner
Stephan Lechner

Reputation: 35154

You could use the max-function in conjunction with group by on the "base" of an incident numner. Not sure, if it works completely as you expect, since I do not know the number system used. But maybe with some adaptions you get the results you want rather easy:

select max(incnumber)
from incidents
group by left(incnumber,7)

Upvotes: 0

Related Questions