Cursor or While Loop or neither?

Hi,
I am looking for a way to pick specific records from a view I have
created. The data from my view looks like this:
machineid serverdate kioskdate kiosktime
ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
02:02:28.000
ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
02:02:24.000
ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
02:02:24.000
ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
02:02:01.000
ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
02:02:23.000
ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
02:02:23.000
ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
02:02:00.000
ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
02:02:29.000
ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
02:02:30.000
ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
02:02:29.000
ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
02:02:29.000
ITG70000008 2004-09-30 01:59:46.000 2004-09-27 00:00:00.000 1900-01-01
02:02:28.000
I would like to perform a select which displays every distinct
machineid ordered by serverdate, so the above resultset would look
like this:
ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
02:02:28.000
ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
02:02:01.000
ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
02:02:00.000
ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
02:02:29.000
I have tried using select distinct machine, serverdate, kioskdate from
V_MyView however this still returns every single result. Do I need to
use a Cursor or a while..loop?
Thanks
Scott
This is just a guess, but perhaps you are loooking for something like this?
SELECT machineid, MAX(serverdate) as serverdate,
MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
FROM V_MyView
By the way, why do you store kiosk 'date' and 'time' in two different
columns?
Keith
"scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:3af9ea3d.0409300540.37b38151@.posting.google.c om...
> Hi,
> I am looking for a way to pick specific records from a view I have
> created. The data from my view looks like this:
> machineid serverdate kioskdate kiosktime
> ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:28.000
> ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:24.000
> ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:24.000
> ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:01.000
> ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:23.000
> ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:23.000
> ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:00.000
> ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:29.000
> ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:30.000
> ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:29.000
> ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:29.000
> ITG70000008 2004-09-30 01:59:46.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:28.000
> I would like to perform a select which displays every distinct
> machineid ordered by serverdate, so the above resultset would look
> like this:
> ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:28.000
> ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:01.000
> ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:00.000
> ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:29.000
> I have tried using select distinct machine, serverdate, kioskdate from
> V_MyView however this still returns every single result. Do I need to
> use a Cursor or a while..loop?
> Thanks
>
> Scott
|||I think he will also need a GROUP BY clause.
SELECT machineid, MAX(serverdate) as serverdate,
MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
FROM V_MyView
GROUP BY machineid
"Keith Kratochvil" wrote:

> This is just a guess, but perhaps you are loooking for something like this?
> SELECT machineid, MAX(serverdate) as serverdate,
> MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
> FROM V_MyView
> By the way, why do you store kiosk 'date' and 'time' in two different
> columns?
> --
> Keith
>
> "scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> news:3af9ea3d.0409300540.37b38151@.posting.google.c om...
>
|||Yep, missed that one. I hit Send too quickly.
Keith
"mthomason" <mthomason@.discussions.microsoft.com> wrote in message
news:A25A8079-9D5E-4DAB-A56B-E4BA779DF386@.microsoft.com...[vbcol=seagreen]
> I think he will also need a GROUP BY clause.
> SELECT machineid, MAX(serverdate) as serverdate,
> MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
> FROM V_MyView
> GROUP BY machineid
> "Keith Kratochvil" wrote:
this?[vbcol=seagreen]
|||On 30 Sep 2004 06:40:27 -0700, scott wrote:

>Hi,
>I am looking for a way to pick specific records from a view I have
>created. The data from my view looks like this:
(snip)
>I would like to perform a select which displays every distinct
>machineid ordered by serverdate, so the above resultset would look
>like this:
(snip)
Hi Scott,
Looking at your expected results, it looks as if you want to find the row
with the highest serverdate for each machineid, then display all columns
of that row. Correct?

>I have tried using select distinct machine, serverdate, kioskdate from
>V_MyView however this still returns every single result. Do I need to
>use a Cursor or a while..loop?
Neither. Try the following SQL:
SELECT machineid, serverdate, kioskdate, kiosktime
FROM YourView AS a
WHERE NOT EXISTS
(SELECT *
FROM YourView AS b
WHERE b.machineid = a.machineid
AND b.serverdate > a.serverdate)
Or try the following alternative:
SELECT a.machineid, a.serverdate, a.kioskdate, a.kiosktime
FROM YourView AS a
INNER JOIN (SELECT machineid, MAX(serverdate)
FROM YourView
GROUP BY machineid) AS b(machineid, maxserverdate)
ON a.machineid = b.machineid
AND a.serverdate = b.maxserverdate
(Both queries are untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for all your replies, Keith is the winner, can't beleive I
forgot about using "max"!
Rgds,
Scott
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message news:<#LLMqlwpEHA.592@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Yep, missed that one. I hit Send too quickly.
> --
> Keith
>
> "mthomason" <mthomason@.discussions.microsoft.com> wrote in message
> news:A25A8079-9D5E-4DAB-A56B-E4BA779DF386@.microsoft.com...
> this?
sql