SQL query, vise min() vrijednosti iz tablice

SQL query, vise min() vrijednosti iz tablice 11.06.2010. u 20:34 · pre 199 meseci #1

Zdravo!!

Evo jedan zadatkic ako tko ima vremena da malo promozga.

Pravim neki programcic za pracenje vremena po krugovima u moto sportu i sad je zapelo. Trebam izvadit najbrze krugove (laps) iz baze ali po userima i za odredeni track_id. U laps tablici imamo vise track-ova i vise usera sa unesenim vremenima u 3 kolone (min, sec i milli) npr. 1 25 145 .
Evo, ja probavam vec satima i ne ide mi nikako.

Dosao sam do ovdje:

Code:

SELECT lap_id, user_id, track_id, min, sec, milli
FROM laps
WHERE concat(`min`, `sec`, `milli`) =
  (SELECT MIN(concat(`min`, `sec`, `milli`))
  FROM laps
  WHERE track_id = 1
)

i to je ok za najnize tj. najbrze uneseno vrijeme.

Sad samo probao ugurat GROUP BY user_id da mi vrati najniza vremena po useru ali nema sanse:

Code:

SELECT lap_id, user_id, track_id, min, sec, milli
FROM laps
WHERE concat(`min`, `sec`, `milli`) =
  (SELECT MIN(concat(`min`, `sec`, `milli`))
  FROM laps
  WHERE track_id = 1
)
GROUP BY user_id

ovo ne valja.

Niti ovaj pristup ne valja:

Code:

SELECT lap_id, user_id, track_id, min, sec, milli
FROM laps
WHERE track_id = 1
GROUP BY user_id
HAVING concat(`min`, `sec`, `milli`) =
  (SELECT MIN(ass) FROM
    (SELECT user_id, track_id, concat(`min`, `sec`, `milli`) as ass
    FROM laps
    WHERE track_id = 1
    GROUP BY user_id) as nesto
)

E kad bi to na neku foru proradilo onda bi jos trebalo stavit order by concat(`min`, `sec`, `milli`)

Kako to izvaesti?

I sta mislite, jel dobro spremati vremena u 3 kolone? Mozda ima koji bolji nacin?

Hvala unaprijed!!

Re: SQL query, vise min() vrijednosti iz tablice 11.06.2010. u 22:40 · pre 199 meseci #2

A možda samo ovako:

Code:
SELECT lap_id, user_id, track_id, MIN(concat(`min`, `sec`, `milli`))
FROM laps
WHERE track_id=1
GROUP BY user_id

Re: SQL query, vise min() vrijednosti iz tablice 11.06.2010. u 23:11 · pre 199 meseci #3

Code:

SELECT lap_id, user_id, track_id, min, sec, milli, MIN(concat(min, sec, milli))
FROM laps
WHERE track_id = 1
GROUP BY user_id

Ne, ovo ne radi, ne znam zasto. Dobijam neispravne podatke.

MIN(concat(`min`, `sec`, `milli`)) je ok ali ostalo nije. Niti lap_id nije dobar niti min, sec, milli..

Ne znam.

Re: SQL query, vise min() vrijednosti iz tablice 12.06.2010. u 10:59 · pre 199 meseci #4

Probaj onda ovako, mada verovatno ima neko elegantnije rešenje:

Code:
SELECT lap_id, track_id, `min`, `sec`, milli, best_results.best
FROM laps AS l,
(SELECT user_id, MIN(concat(`min`, `sec`, `milli`)) AS best
FROM laps
WHERE track_id=1
GROUP BY user_id) AS best_results
WHERE l.user_id = best_results.user_id AND concat(l.`min`, l.`sec`, l.`milli`) = best_results.best

1

Re: SQL query, vise min() vrijednosti iz tablice 12.06.2010. u 11:09 · pre 199 meseci #5

Probaj ovako:

Code:
SELECT lap_id, user_id, track_id, MIN(concat(`min`, `sec`, `milli`)) as `lap time`
FROM laps
WHERE track_id=1
GROUP BY user_id,lap_id
ORDER BY lap_id, `lap time`

Re: SQL query, vise min() vrijednosti iz tablice 12.06.2010. u 11:17 · pre 199 meseci #6

Radi ko blesavo :P !!

Puno puno hvala!

Sad jos samo moram skuzit sta se tu desava i sve 5.

Re: SQL query, vise min() vrijednosti iz tablice 12.06.2010. u 11:18 · pre 199 meseci #7

@Tudfa, hvala na odgovoru ali ne ide tako.

Re: SQL query, vise min() vrijednosti iz tablice 12.06.2010. u 21:00 · pre 199 meseci #8

Pozdrav,

Ovo mora da radi
- top 10 lapova za odredjeni track

Code:
SELECT
    laps.lap_id,
    laps.user_id,
    MIN(CONCAT(laps.`min`, ':', laps.`sec`, '.', laps.`milli`)) as `lap_time`
FROM
    laps
WHERE
    laps.track_id =  '1'
GROUP BY
    laps.user_id
ORDER BY
    `lap_time`, `lap_id`
LIMIT 10;

- najbolji lapovi po stazama

Code:
SELECT
    lap_id,
    track_id,
    user_id,
    lap_time
FROM
(
    SELECT
        laps.lap_id,
        laps.track_id,
        laps.user_id,
        MIN(CONCAT(laps.`min`, ':', laps.`sec`, '.', laps.`milli`)) as lap_time
    FROM
        laps
    GROUP BY
        laps.track_id,
        laps.user_id
    ORDER BY lap_time, lap_id
) AS lap_times
GROUP BY
    track_id

ako neko ima elegantnije resenje, zaista bi me zanimalo

Re: SQL query, vise min() vrijednosti iz tablice 13.06.2010. u 09:26 · pre 199 meseci #9

g. Rajko super je ovo! Puno hvala!

Forum kao aplikacija na telefonu

Srodne teme