1

I was hoping for some help on a query I am stuck on.

Basically I have the following table 

id | tmpid | tmpyear | tmpone | tmptwo | tmpthree | tmptype 

1  | 2     | 9       | 33     | 26     | 12       | profit

2  | 2     | 9       | 32     | 10     | 2        | profit

3  | 2     | 9       | 55     | 14     | 10       | loss

4  | 2     | 9       | 16     | 12     | 4        | loss

5  | 2     | 10      | 12     | 19     | 3        | profit

I have no idea how to format a query which would return the rows with id 2 and id 5

Basically I want to select the whole row but I want distinct tmpid,tmpyear and tmptype

Queries I tried

Select * from table where tmpid = 2 and tmptype = 'profit'

This returns 3 rows however I dont want to return where tmpyear is duplicated which this query will return id 1 and 2. In case of duplicated year I would rather the query only returns the higher id ( returns id 2 and ignores id 1 ).

Select distinct tmpid,tmpyear,tmptype where tmptype = 'profit'

This query is nearly perfect , it returns the following 

tmpid | tmpyear | tmptype 

2     | 9       | profit

2     | 10      | profit

So we have got rid of the one row with duplicate tmpyear however I also need the other information such as tmpone, tmptwo , tmpthree which is missing from this .

Apologies for the confusing question, I'm not sure how else I can explain it.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Fatalerror
  • 23
  • 3
  • Which tmpone etc should be returned when there’s multiple? – Sami Kuhmonen Jan 02 '20 at 17:48
  • the last inserted of the multiple rows so the one with the higher tmpid therefore in terms of the example all the values on the same row as id 2 should be returned. – Fatalerror Jan 02 '20 at 17:51
  • Read this please: [sql-select-only-rows-with-max-value-on-a-column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – Paul Spiegel Jan 02 '20 at 18:03

2 Answers2

1

You can do it with NOT EXISTS:

select t.* from tablename t
where t.tmptype = 'profit'
and not exists (
  select 1 from tablename
  where tmpid = t.tmpid and tmpyear = t.tmpyear and tmptype = t.tmptype and id > t.id
)

See the demo.
Results:

| id  | tmpid | tmpyear | tmpone | tmptwo | tmpthree | tmptype |
| --- | ----- | ------- | ------ | ------ | -------- | ------- |
| 2   | 2     | 9       | 32     | 10     | 2        | profit  |
| 5   | 2     | 10      | 12     | 19     | 3        | profit  |
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Hi , this query is working well. Can you please just explain to me how this works. Just a little explanation about what not exists means and how the query decides which rows it should keep. Thanks – Fatalerror Jan 02 '20 at 21:55
  • This is how you can read the code: you want any row of the table with `tmptype = 'profit'` for which there does not exist another row with the same tmpid, tmpyear and tmptype that has a higher id (because you want the highest id). – forpas Jan 02 '20 at 21:59
  • 1
    Hi Forpas, Thank you very much for the explanation and the answer. I will definitely try this tomorrow on my project and I will make sure to mark the answer then. Again appreciate the help! – Fatalerror Jan 02 '20 at 22:05
0

You could use a subquery for max id 

select  * from mytable m
inner join (
  select  max(id) as id , tmpid, tmpyear 
  from  mytable 
  where  tmptype = 'profit'
  group by   tmpid, tmpyear 
) t on t.id = m.id 
where m.tmpid = 2
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Hi Thanks, I am getting 'tmpid in where clause is ambiguous . Is this simply just change ' where tmpid = 2 ' to 'where m.tmpid = 2' ? Also could you explain a little how this query decides which row to keep when multiple similar rows are present. Thanks. – Fatalerror Jan 02 '20 at 22:02
  • yes m.tmpid avoid ambiguity ... then the choise for the row to keep when multiple similar rows are present in based on the subquery that for the same tmpid, tmpyear select only the max(id) in this way you have only t one rows .... joining this result with the main query you select only the corresponding rows .. hope this is useful .. answer updated for m.tmpid – ScaisEdge Jan 03 '20 at 06:06
  • Hi, how does this differ to the query posted above by Forpas . Is there any performance improvement to using this query over the other ? Appreciate the help. – Fatalerror Jan 03 '20 at 08:06
  • the two query use different logic in subquery .. for performance you should try on your db .. a check yourself .. – ScaisEdge Jan 03 '20 at 08:22