[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



On Wed, 30 May 2007, Kristian Nørgaard wrote:

Min SQL syntax er lidt rusten...
- hvordan får jeg en liste med alle bøgers billigste pris og salgsted?

Tabel:

--id-----bogtitel ------  salgsted ------ 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ørder'                 'Metro'          '50'


svaret må gerne være MYSQL kompatibelt

Det ved jeg ikke om dette er, men SQL-korrekt skulle det være:

SELECT a.id, b.bogtitle, a.salgsted, b.pris
FROM bog a, (SELECT bogtitle, MIN(pris) AS pris FROM bog GROUP BY bogtitle) b
WHERE a.bogtitle=b.bogtitle AND a.pris=b.pris;

Problemet med GROUP BY er at du ikke kan få kolonnen med salgsted med.
Derfor laver jeg den så i to tempi.
Den "anden" tabel (b) kan også være en temporær tabel.

Tabel b indeholder kun bogtitle og mindste pris, og det bliver så join'et med hele bog-tabellen.

Denne metode fordre at der er en unique constrain på bogtitle, salgsted og pris.

Hvis to salgsteder har samme mindste pris, vises de begge. (hvilket vel også er matematisk korrekt)

/hans
--
Horsebakken 78, DK-2400 København NV, Phone: +45 2264 8020
-------------------------------------------------------------
1. Hvad tid åbnede og lukkede bakken.dk onsdag d. 16. maj 2007?
2. Hvor ligger Nakskov?
3. Er ovenstående spørgsmål overhovedet relevante?


 
Home   Subscribe   Mail Archive   Index   Calendar   Search

 
 
Questions about the web-pages to <www_admin>. Last modified 2007-06-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] *