-1

My database table

I have this history table that maitains duplicate Panel_Number and its history. I will need your help in getting a query which will give me distinct Panel_Number with its max(version) from the table.

eg:- Panel 0G3101 has 3 occurences in the table with different version 1,2,3. I only need to show in the resultset the Panel 0G3101 WITH MAX(VERSION) which is 3 in this case.

Likewise I need all distinct panel numbers with their max version .

APC
  • 144,005
  • 19
  • 170
  • 281
MayurS
  • 1
  • 1
  • 1
    If your requirement is for `[mysql]` database don't include the `[oracle]` tag. They are different products, and for this task they may have different solutions. – APC Aug 30 '17 at 05:30
  • 1
    You can may be look into this, this has helped me previously. Hope it helps for you too. https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column – Amal Aug 30 '17 at 05:58

3 Answers3

0

Here is an SQL fiddle I made for you. I added some arbitrary data, but the query will work properly with you.

The query should be:

SELECT
    id, 
    panel_number, 
    max(version) 
FROM 
    PANEL
GROUP BY
    panel_number

The result is like:

| id | panel_number | max(version) |

|----|--------------|--------------|

|  1 |       0G3101 |            3 |

|  4 |       0G3102 |            2 |
Balagurunathan Marimuthu
  • 2,927
  • 4
  • 31
  • 44
alim1990
  • 4,656
  • 12
  • 67
  • 130
  • Thanks. but this does not work entirely as required. I might have not explained my requirement precisely. In the image, for panel no 0G3101, 0G3106, 0G3107, 0G3108 I am getting the version correctly but the latest records inserted which means the query shud return ids- 61.62,63,64 (which are at the end of the table ) – MayurS Aug 30 '17 at 05:32
0

You can use the Group by clause to solve your issue.

SELECT  panel_number, max(version) FROM PANEL GROUP BY panel_number
Arun Vitto
  • 163
  • 10
  • Sorry bud. I will need the lastest version of the panel number inserted which in this case is ids- 61.62,63,64 (which are at the end of the table ) – MayurS Aug 30 '17 at 05:33
  • Try the following one `SELECT panel_number, version,max(id) FROM PANEL GROUP BY panel_number ` – Arun Vitto Aug 30 '17 at 05:56
  • Update your question so we can help, and make some changes to the fiddle so we can know what you exactly want. – alim1990 Aug 30 '17 at 06:21
-1

Try these..work 100% click on query..

SELECT  panel_number, MAX(version) FROM PANEL GROUP BY panel_number
Sandeep Suthar
  • 57
  • 1
  • 12
  • This does not meet the requirement, which is to select the record with the highest version for each business key (`panel_number`). – APC Aug 30 '17 at 05:27