Prev: Machine Name change not being recognized by SQL
Next: String Manipulation Func
From: Erland Sommarskog on 20 Jun 2010 09:31
AussieRules (nospam(a)nospam.com) writes:
> Thanks for your help.. Yes I took it on, but because I figured I could
> learn... no hard having a go..
>
> The select now works, and the date string is returned as the value
> '20100614'. I assume that this 14/6/2010 (uk format)..

That is indeed 2010-06-14, yes.

> Is there a way to convert this char, into a date format,

I see that you have already found the convert function.

> I modified the code to suit my db, and ended up with :
>
> SELECT TOP 5 sku
> FROM (SELECT a.sku, COUNT(*) AS cnt
> FROM tbl_pick a
> WHERE a.sku <> '8845'
> AND EXISTS (SELECT *
> FROM tbl_pick b
> WHERE a.job_id = b.job_id
> AND a.sku = '8845')) AS x
> ORDER BY cnt DESC
>
> However I get the following:
>
> Msg 8120, Level 16, State 1, Line 3
> Column 'tbl_pick.sku' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.

As I said, it was not tested for correctness. But as the error message
said, I happened to forget the GROUP BY clause.

> I can see that the error comes from the select statement that has the
> count(*) code, but not sure where to put the group by clause....

Well, what possible places are there, and which make sense? :-)


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: Machine Name change not being recognized by SQL
Next: String Manipulation Func