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
ScottThis 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.com...
> 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.com...
> > 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
>|||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...
> 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.com...
> > > 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
> >
> >|||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>...
> 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...
> > 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.com...
> > > > 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
> > >
> > >