[an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] (none) [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] (none) [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive]
 
[an error occurred while processing this directive] [an error occurred while processing this directive]
Skåne Sjælland Linux User Group - http://www.sslug.dk Home   Subscribe   Mail Archive   Forum   Calendar   Search
MhonArc Date: [Date Prev] [Date Index] [Date Next]   Thread: [Date Prev] [Thread Index] [Date Next]   MhonArc
 

Re: [PROGRAMMERING] [SQL] group by hjælp



[klip] 
> 
> Følgende eksperiment er lavet i MySQL
> 
> mysql> select * from t2;
> +----+-----------------+-----------+------+
> | id | bogtitel        | salgssted | pris |
> +----+-----------------+-----------+------+
> |  1 | Linux A-Z       | Amazon    |  200 |
> |  2 | Linux A-Z       | Metro     |  180 |
> |  3 | Mit liv som Tux | Bogladen  |   99 |
> |  4 | Mit liv som Tux | Amazon    |  199 |
> |  5 | N               | Metro     |   50 |
> +----+-----------------+-----------+------+
> 5 rows in set (0.00 sec)
> 
> Dette passer til dit oplæg.
> 
> Prøv nu:
> mysql> select id,bogtitel,salgssted,min(pris) from t2 group by bogtitel;
> +----+-----------------+-----------+-----------+
> | id | bogtitel        | salgssted | min(pris) |
> +----+-----------------+-----------+-----------+
> |  1 | Linux A-Z       | Amazon    |       180 |
> |  3 | Mit liv som Tux | Bogladen  |        99 |
> |  5 | N               | Metro     |        50 |
> +----+-----------------+-----------+-----------+
> 3 rows in set (0.00 sec)
> 
> Sådan læste jeg dit spørgsmål.
> 

Men det giver jo netop ikke det rigtige - Det er jo ikke Amazon, der sælger
bogen til 180.

Et forslag til en korrekt query er:

mysql> select f.id, f.bogtitel, f.salgssted, f.pris from (select bogtitel,
min(pris)as minpris from test group by bogtitel) as x inner join test as f
on f.bogtitel = x.bogtitel and f.pris = x.minpris;

+----+-----------------+-----------+------+
| id | bogtitel        | salgssted | pris |
+----+-----------------+-----------+------+
|  2 | Linux A-Z       | Metro     |  180 |
|  3 | Mit liv som Tux | Bogladen  |   99 |
|  5 | N               | Metro     |   50 |
+----+-----------------+-----------+------+
3 rows in set (0.03 sec)

Query'en fandt jeg ikke selv på :)
Kig på 
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per
-group-in-sql/


/Lars
    



 
Home   Subscribe   Mail Archive   Index   Calendar   Search

 
 
Questions about the web-pages to <www_admin>. Last modified 2007-07-01, 02:01 CEST [an error occurred while processing this directive]
This page is maintained by [an error occurred while processing this directive]MHonArc [an error occurred while processing this directive] # [an error occurred while processing this directive] *