|
 |
sql_language thread: Why The Junk In The Message
Message #1 by "Joshua N" <orancrdba@h...> on Tue, 09 Apr 2002 08:47:56 -0400
|
|
Hi,
When I go to the link below and browse the posted message it is filled with
junk characters. Is there a way to view it more legibly?
Thanks
>From: "sql language digest" <sql_language@p...>
>Reply-To: "sql language" <sql_language@p...>
>To: "sql_language digest recipients" <sql_language@p...>
>Subject: sql_language digest: April 08, 2002
>Date: Tue, 09 Apr 2002 00:02:06 +0100
>
>-----------------------------------------------
>When replying to the digest, please quote only
>relevant material, and edit the subject line to
>reflect the message you are replying to.
>-----------------------------------------------
>
>The URL for this list is:
>http://p2p.wrox.com/list.asp?list=sql_language
>SQL_LANGUAGE Digest for Monday, April 08, 2002.
>
>1. Syntax to query top 1 record of several sets at once?
>
>----------------------------------------------------------------------
>
>Subject: Syntax to query top 1 record of several sets at once?
>From: "Michael Bunger" <michael@r...>
>Date: Mon, 8 Apr 2002 11:11:05 -0500
>X-Message-Number: 1
>
>This is a multi-part message in MIME format.
>
>------=_NextPart_000_0021_01C1DEEE.1436E850
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>
>Hello,
>
>I'm not sure of the syntax necessary to give me my desired result, nor am I
>sure that it is possible (but, I must believe that there is *some* way to
>do
>it).
>
>
>TABLE STRUCTURE
>===============
>Basically, I have an audit table that holds a history of changes to certain
>data I am tracking. For example,
>
>ID (identity primary key)
>dt (Date)
>gn (Group Number)
>pgIDOld (OldProductGroupID)
>pgIDNew (NewProductGroupID)
>
>Here's some sample data.
>ID dt gn pgIDOld pgIDNew
> 1 3/23/02 4 <NULL> 67
> 2 3/25/02 4 67 68
> 3 3/27/02 4 68 56
> 4 3/23/02 5 <NULL> 77
> 5 3/25/02 5 77 78
> 6 3/27/02 5 78 76
>
>DESIRED RESULT FROM QUERY
>=========================
>What I would like to be able to do is to pass a date to the stored
>procedure, then be returned a recordset containing the one record from each
>group number (gn) that is closest to the date parameter but does not exceed
>it.
>
>For example, if I passed in the date 03/22/02, I would expect to see the
>following recordset returned:
>ID dt gn pgIDOld pgIDNew
>(No Records Returned)
>
>If I passed in the date 03/24/02, I would expect to see the following
>recordset returned:
>ID dt gn pgIDOld pgIDNew
> 1 3/23/02 4 <NULL> 67
> 4 3/23/02 5 <NULL> 77
>
>If I passed in the date 03/26/02, I would expect to see the following
>recordset returned:
>ID dt gn pgIDOld pgIDNew
> 2 3/25/02 4 67 68
> 5 3/25/02 5 77 78
>
>If I passed in the date 03/28/02 (or later), I would expect to see the
>following recordset returned:
>ID dt gn pgIDOld pgIDNew
> 3 3/27/02 4 68 56
> 6 3/27/02 5 78 76
>
>I've tried using the GROUP syntax, but, that won't work since you must
>include a returned colum in an aggregate function. Thus, if I use a MAX,
>MIN, etc on the pgIDOld and pgIDNew column, the result is useless since it
>is applied to the whole group of each gn instead of returning a specific
>value from a record (which is what I must have).
>
>Any suggestions/comments will be greatly appreciated.
>
>Thanks,
>Michael Bunger
>
>------=_NextPart_000_0021_01C1DEEE.1436E850
>Content-Type: text/html;
> charset="iso-8859-1"
>Content-Transfer-Encoding: quoted-printable
>
><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
><HTML><HEAD>
><META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html;
>charset=3Diso-8859-1">
>
>
><META content=3D"MSHTML 6.00.2462.0" name=3DGENERATOR></HEAD>
><BODY>
><DIV><FONT face=3DArial size=3D2>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>Hello,</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2></FONT></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>I'm not sure
>of the syntax necessary to give me my desired result, nor am I sure that
>it is
>possible (but, I must believe that there is *some* way to do
>it).</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2></FONT></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2></FONT></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>TABLE
>STRUCTURE</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT></SPAN></DIV
> >
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>Basically, I
>have an audit table that holds a history of changes to certain data
>I am
>tracking. For example,</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2></FONT></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>ID (identity
>primary key)</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>dt
>(Date)</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>gn (Group
>Number)</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>pgIDOld
>(OldProductGroupID)</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>pgIDNew
>(NewProductGroupID)</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2></FONT></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>Here's some
>sample data.</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>ID dt
>gn pgIDOld pgIDNew</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"><SPAN
>class=3D318250415-08042002><FONT face=3D"Courier New">
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"><FONT
>size=3D2> 1 3/23/02
>4 <NULL> 67 </FONT>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2> 2 3/25/02
>4 67 68</FONT><
>/SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2> 3 3/27/02
>4 68
>56</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"><FONT
>size=3D2> 4
>3/23/02 5 <NULL> 77
></FONT>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2> 5
>3/25/02 5 77 &nb
>sp; 78</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2> 6
>3/27/02 5 78 &nb
>sp;
>76</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3DArial
>size=3D2></FONT></SPAN> </DIV></SPAN></DIV></FONT></SPAN></DIV></
SPA
>N></DIV></SPAN></DIV></FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2>DESIRED RESULT FROM QUERY</FONT></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>=3D=3D=3D</FONT></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2>What I would like to be able to do is to pass a date to the
>stored
>procedure, then be returned a recordset containing the one record
>from each
>group number (gn) that is closest to the date parameter but does not
>exceed
>it.</FONT></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>face=3DArial size=3D2></FONT></SPAN></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>face=3DArial><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>face=3D"Courier New" size=3D2>For example, if I passed in the date
>03/22/02, I
>would expect to see the following recordset
>returned:</FONT></SPAN></SPAN></DIV>
><DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>ID dt
>gn pgIDOld pgIDNew</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier
>New"></FONT><SPAN
>class=3D318250415-08042002><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002>
><DIV>
><DIV>
><DIV>
><DIV><FONT face=3D"Courier New"><FONT size=3D2>(<SPAN
>class=3D318250415-08042002>No
>Records Returned)</SPAN></FONT></FONT></DIV>
><DIV><FONT face=3D"Courier New"><FONT size=3D2><SPAN
>class=3D318250415-08042002></SPAN></FONT></FONT></SPAN></SPAN></SPAN></SP
>AN></SPAN></SPAN> </DIV></DIV></DIV></DIV></DIV></DIV></SPAN>&
lt;/SPAN>
></DIV></FONT></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2>If I passed in the date 03/24/02, I would expect to see
>the
>following recordset returned:</FONT></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT
>size=3D2>ID
>dt gn pgIDOld
>pgIDNew</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT
>size=3D2> 1
>3/23/02 4 <NULL> 67
></FONT>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT size=3D2> 4
>3/23/02 5 <NULL> 77
></FONT></DIV>
><DIV>
><DIV>
><DIV>
><DIV></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN><FONT
>size=3D2></FONT> </DIV>
><DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2>If I passed in the date 03/26/02, I would expect to see
>the
>following recordset returned:</FONT></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT
>size=3D2>ID
>dt gn pgIDOld
>pgIDNew</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"><SPAN
>class=3D318250415-08042002><FONT face=3D"Courier New">
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New">
><DIV><SPAN class=3D318250415-08042002><FONT
>size=3D2> 2
>3/25/02
>4 67 68</FONT><
>/SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>face=3D"Courier New"><SPAN class=3D318250415-08042002><FONT
>size=3D2> 5
>3/25/02 5 77 &nb
>sp; 78</FONT></SPAN></DIV>
><DIV>
><DIV>
><DIV><FONT size=3D2><FONT face=3DArial></FONT><FONT
>face=3DArial></FONT></FONT> </DIV>
><DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2>If I passed in the date 03/28/02 (or later), I would
>expect to see
>the following recordset returned:</FONT></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2>ID dt
>gn pgIDOld pgIDNew</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"><SPAN
>class=3D318250415-08042002><FONT face=3D"Courier New">
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New">
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"><SPAN
>class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2> 3 3/27/02
>4 68
>56</FONT></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New">
><DIV><SPAN class=3D318250415-08042002>
><DIV><SPAN class=3D318250415-08042002><FONT face=3D"Courier New"
>size=3D2> 6
>3/27/02 5 78 &nb
>sp;
>76</FONT></SPAN></DIV></SPAN></DIV></FONT></SPAN></DIV></SPAN></DIV>
</SPA
>N></FONT></SPAN><SPAN
>class=3D318250415-08042002><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT face=3D"Courier New"><FONT
>face=3DArial></FONT></FONT></SPAN></SPAN></SPAN><SPAN
>class=3D318250415-08042002><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT face=3D"Courier New"><FONT
>face=3DArial></FONT></FONT></SPAN></SPAN></SPAN></FONT></SPAN></FONT></SP
>AN></FONT></SPAN></DIV></DIV></DIV></SPAN></SPAN></DIV></DIV></FONT&
gt;</SPA
>N></SPAN></SPAN><SPAN
>class=3D318250415-08042002><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT face=3D"Courier New"><FONT
>face=3DArial></FONT></FONT></SPAN></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2></FONT></SPAN></SPAN></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT size=3D2>I've tried using the GROUP
>syntax, but,
>that won't work since you must include a returned colum in an aggregate
>function. Thus, if I use a MAX, MIN, etc on the pgIDOld and
>pgIDNew
>column, the result is useless since it is applied to the whole group of
>each gn instead of returning a specific value from a record (which
>is what
>I must have).</FONT></SPAN></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2></FONT></SPAN></SPAN></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT size=3D2>Any suggestions/comments will
>be greatly
>appreciated.</FONT></SPAN></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2></FONT></SPAN></SPAN></SPAN> </DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT
>size=3D2>Thanks,</FONT></SPAN></SPAN></SPAN></DIV>
><DIV><SPAN class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><SPAN
>class=3D318250415-08042002><FONT size=3D2>Michael
>Bunger</FONT></SPAN></SPAN></SPAN></DIV></DIV></DIV></FONT></SPAN></DIV
><
>/FONT></SPAN></FONT></SPAN></DIV></SPAN></SPAN></DIV></DIV></DIV></D
IV></
>DIV></DIV></DIV></SPAN></SPAN></DIV></FONT></SPAN></FONT></SPAN></DI
V></S
>PAN></DIV></FONT></DIV></BODY></HTML>
>
>------=_NextPart_000_0021_01C1DEEE.1436E850--
>
>
>
>
>---
>
>END OF DIGEST
>
_________________________________________________________________
Join the world?s largest e-mail service with MSN Hotmail.
http://www.hotmail.com
|
|
|
View Cart
