/    Sign up×
Community /Pin to ProfileBookmark

MIN VALUE Get ID of that row

I’m doing a SQL search counting and summing all rows in a table and grouping by a common index.

I want to get the earliest date by using MIN(entryDate) but then want to get this ID (entryID).

If I do MIN(entryID) it doesn’t mean it’s the same line as entryDate.

Any ideas?

to post a comment
PHP

11 Comments(s)

Copy linkTweet thisAlerts:
@sibertJan 26.2021 — > @kiwis80#1627173 I want to get the earliest date by using MIN(entryDate) but then want to get this ID (entryID).

Not that elegant, but it works:

``<i>
</i>SELECT id, min(entrydate) FROM test
GROUP BY 1 ORDER BY 2 LIMIT 1<i>
</i>
``


https://www.db-fiddle.com/f/f8nZn5LQfbmKxAdxX2fdej/55
Copy linkTweet thisAlerts:
@kiwisauthorJan 26.2021 — @sibert#1627174

Doesn't if I then do the same for MAX?

I get the correct MIN MAX entry dates, I just need to get their ID's somehow?
Copy linkTweet thisAlerts:
@sibertJan 26.2021 — Both max and min and id (just click on Run):

https://www.db-fiddle.com/f/f8nZn5LQfbmKxAdxX2fdej/56
Copy linkTweet thisAlerts:
@kiwisauthorJan 26.2021 — But I need it in the same QUERY with other filters
Copy linkTweet thisAlerts:
@kiwisauthorJan 26.2021 — Tried this with no luck

``<i>
</i> MIN(g.entryDate) as FirstEntry,
MAX(g.entryDate) as LastEntry,

(SELECT g.ID FROM
entries WHERE MIN(g.entryDate) limit 1) as AA,
(SELECT g.ID FROM
entries WHERE MAX(g.entryDate) limit 1) as BB<i>
</i>
``
Copy linkTweet thisAlerts:
@JennieMillerJan 26.2021 — i also fine this query.
Copy linkTweet thisAlerts:
@sibertJan 26.2021 — ``<i>
</i>SELECT id FROM test
WHERE entrydate=(SELECT min(entrydate) FROM test)<i>
</i>
``

https://www.db-fiddle.com/f/f8nZn5LQfbmKxAdxX2fdej/59
Copy linkTweet thisAlerts:
@NogDogJan 26.2021 — I'm not sure you actually gain much by doing it in a single query, so I might be tempted to just do it as two separate queries. But _maybe_...
<i>
</i>select
first.id as first_id,
first.entryDate as first_date,
last.id as last_id,
last.entryDate as last_date
from (select id, entryDate from entries order by entryDate limit 1) as first
left join (select id, entryDate from entries order by entryDate desc limit 1) as last

But then you're really just wrapping 2 queries inside of 1 query, so it may be no less efficient to just make those 2 queries separately (or so minimally different that keeping the code simple is worth it).
Copy linkTweet thisAlerts:
@NogDogJan 26.2021 — @NogDog#1627198

PS: Not sure if that join might have to have an ON clause, in which case:
<i>
</i>left join (select id, entryDate from entries order by entryDate desc limit 1) as last on true
Copy linkTweet thisAlerts:
@sibertJan 26.2021 — ``<i>
</i>SELECT
(SELECT id FROM test WHERE entrydate=(SELECT min(entrydate) FROM test)) min,
(SELECT id FROM test WHERE entrydate=(SELECT max(entrydate) FROM test)) max<i>
</i>
``

https://www.db-fiddle.com/f/f8nZn5LQfbmKxAdxX2fdej/61
Copy linkTweet thisAlerts:
@kiwisauthorJan 28.2021 — @sibert#1627205

I forgot to follow this up, this is exactly what I ended up doing.
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 3.29,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 10 SATS,
)...