LJ skrev:
> Hej.
>
> Jeg har en query:
> select plate as ID, count(ID) as OCCUR from IDS group by plate ID by
> OCCUR asc;
>
> Udvælger alle forekomster af ID og tæller hvor mange forekomster der er,
> og skriver disse ud sorteret på antallet af OCCUR.
> Men hvis jeg nu kun vil have udskrevet alle dem hvor OCCUR er større end
> 5 ?
> Jeg har prøvet med diverse WHERE > 5, men uden held.
>
> Mvh. LJ.
>
Hvad du leder efter er "HAVING"
(Jeg formoder du har glemt ordet "ORDER")
select plate as ID, count(ID) as OCCUR
from IDS
group by plate ID
HAVING OCCUR > 5
ORDER BY OCCUR asc
Eller, hvis din sql-variant, (hvilket du har glemt at skrive), ikke
supporterer alias'er i HAVING, (men det gør den vel, når den supporterer
aliaser i ORDER BY
select plate as ID, count(ID) as OCCUR
from IDS
group by plate ID
HAVING count(ID) > 5
ORDER BY OCCUR asc
I Mysql er den korte syntax:
SELECT .. FROM .. WHERE .. GROUP BY .. HAVING .. ORDER BY .. LIMIT ..
MsSql bruger
SELECT TOP .. .. FROM .. WHERE .. GROUP BY .. HAVING .. ORDER BY ...
Den fulde syntax for SELECT i MySql er
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
http://dev.mysql.com/doc/refman/5.0/en/select.html
Leif