1

I have an table like that:

id | name | v (lvl)
11 | Jane | 6
12 | John | 5
13 | Jane | 6
14 | John | 5
15 | Jane | 7
16 | Jane | 5

In my autocomplete form now id like to group the names but get the last value (value with biggest id). In the example above would be 

Jane | 5

I tried with combinations like distinct, group by, order by. But im always get

Jane | 6

or grouped like this and reversed:

Jane | 6 
Jane | 7 
Jane | 5

I would need something like this:

SELECT name,lvl FROM
(
SELECT DISTINCT name, lvl FROM pora WHERE name LIKE 'Jane' ORDER BY lvl DESC
)
GROUP BY name

EDIT: I won't get the highest lvl, i want get the lvl of the highest id, grouped by name. Thats all. My example above would be the best explanation what i like to get.
In the inner query i change the order to DESC for all and in the outer i group it by names. But i get an error for this.

EDIT 2 I finally did at my own. The correct solution (i was already close):

SELECT a.name, a.lvl FROM
(
SELECT DISTINCT name, lvl FROM pora WHERE name LIKE 'Jane' ORDER BY id DESC
)as a
GROUP BY name
HoberMellow
  • 8,318
  • 2
  • 20
  • 19
delato468
  • 474
  • 4
  • 18
  • How you know who is the latest value with biggest id? You say `Jane | 5` Why? ? Are there more names or just want the result from `Jane` ? – Juan Carlos Oropeza Aug 24 '17 at 14:30
  • I ordered the example above with id ASC ;D. I can add the ids if you like. EDIT Yes in the database are more names but in this query i only would have this name. – delato468 Aug 24 '17 at 14:35
  • 1
    Yes please, still you need to explain the logic. I dont know why you choose Jane 5 – Juan Carlos Oropeza Aug 24 '17 at 14:39
  • You have no PRIMARY KEY, which is liable to prove problematic in due course. – Strawberry Aug 24 '17 at 14:41
  • 1
    `... order by id desc limit 1` ? – Oto Shavadze Aug 24 '17 at 14:45
  • @OtoShavadze Would work but there are more names wich can be similar and if its only showing 1 that will be a problem. – delato468 Aug 24 '17 at 14:48
  • @delato468 Your question request a single result, how is that a problem now? – Juan Carlos Oropeza Aug 24 '17 at 14:54
  • can you show us example, when you expect more than 1 row ? – Oto Shavadze Aug 24 '17 at 14:54
  • @JuanCarlosOropeza no i excpect 1 row for lvl but not 1 row for names. – delato468 Aug 24 '17 at 14:56
  • Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) – Juan Carlos Oropeza Aug 24 '17 at 14:57
  • Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Aug 24 '17 at 14:57
  • @JuanCarlosOropeza thats not the same – delato468 Aug 24 '17 at 15:02
  • Yes it is. Check Brian L Cartwright answer. In your case the @N is 1 – Juan Carlos Oropeza Aug 24 '17 at 15:03
  • @OtoShavadze I shared an example in Juan Carlos answer. – delato468 Aug 24 '17 at 15:23
  • Your `EDIT 2` may work but is only because a internal setup of MySQL. I wouldnt recomend that solution. You should check the duplicated answer I provide. – Juan Carlos Oropeza Aug 24 '17 at 16:05
  • For all others wich searching for the same, Juan is talking about https://stackoverflow.com/a/13187418/3844973 this. But in my opinion it's not right. If @JuanCarlosOropeza could adjust it for the example above i can try it and look if it's right. Else don't watch at the dublicate. – delato468 Aug 25 '17 at 06:34

3 Answers3

2

LIKE without % is just =

SELECT *
FROM yourTable
WHERE name = 'Jane'
ORDER BY id DESC
LIMIT 1

But because you mention autocomplete functionality you should use:

WHERE name LIKE 'Jane%'
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Limit is not working because if 2 names can be very similar. For example blablabla1 and blablabla2. If im writing now blabla then i can see only 1 name until i finish the full name. – delato468 Aug 24 '17 at 14:55
  • That wasnt what you ask :( – Juan Carlos Oropeza Aug 24 '17 at 14:58
  • Yeah but i said it's a autocomplete form, it's normal there are more names that can be similar. – delato468 Aug 24 '17 at 15:19
  • You need to learn how to ask a proper question so you get better answers. Otherwise we waste time trying to guess what is what you want. Please check the link I post. – Juan Carlos Oropeza Aug 24 '17 at 15:51
  • I understand the question now, but only because read all the comment. But if you read the text question there is nothing showing you want the last result for each name. I'm just saying is your job make sure the question has all the details. – Juan Carlos Oropeza Aug 24 '17 at 16:03
  • It's in the question... I can repeat for you. "the last value (value with biggest id)" and i did an example "Jane | 5" – delato468 Aug 24 '17 at 16:42
  • That indicate a single result, not multiple result :( – Juan Carlos Oropeza Aug 24 '17 at 17:26
  • Yeah but we talking about names. It doesn't need much entrys to get 2 similar names like Jane doe and Jane smith. It's important to calculate that before things like that destorys the autocomplete function. But i gave to all of you an upvote because your help ;D. Thanks and yes im sorry if not everything were clear, but it's hard for me to ask in another language i didn't learnd well. – delato468 Aug 24 '17 at 17:41
2

To have the latest, you need to have a field dateAdded which stores the date you ran the insert command.

Following which, you use MAX(dateAdded) to get the latest ID (since, as you mentioned, it may decrease as well)

UPDATE:

if ID doesn't decrease, you can always use MAX(ID)

SELECT MAX(id), v from tablename where name = 'Jane'

UPDATE:

This has been tested:
SELECT ID, v from tableName where ID = (SELECT MAX(ID) as ID from tableName where name like '%Jane%')

vat69
  • 123
  • 1
  • 10
1

Try the following query (h/t @lamak)

WITH CTE AS
(
    SELECT *,
           RN = ROW_NUMBER() OVER(PARTITION BY name
                                  ORDER BY [id] DESC)
    FROM poro
)
SELECT *
FROM CTE
WHERE RN = 1
kazzi
  • 524
  • 9
  • 33