Query advice

I need a suggestion.
I have a table with
Columns
(Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date) here
Rec_ID and Rec_date are primary key
I need a resultset out of this table which has columns Rec_ID,
Amt_Recieved, Amt_given from the day previous to Rec_Date and Rec_Status,
Rec_Frequency , Rec_date corresponds to the Rec_date
For Eg.
(Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
1, 100,50,A,5,01/21/2005
1, 200,60,B,6,01/22/2005
1, 300,70,C,7,01/23/2005
2, 200,45,B,6,01/23/2005
2, 250,50,C,4,01/24/2005
I need out of it
1, 100,50,B,6,01/22/2005
1, 200,60,C,7,01/23/2005
1, 300,70,B,6,01/23/2005
2, 200,45,C,4,01/24/2005
I have tried inline queries for each column but it takes a lot of time.
Any advice shall be appreciated.
Thanks,
siajset dateformat mdy
create table #temp(Rec_ID int, Amt_Recieved int, Amt_given int,
Rec_Status char(1), Rec_Frequency int, Rec_date
smalldatetime)
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 100,50,'A',5,'01/21/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 200,60,'B',6,'01/22/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 300,70,'C',7,'01/23/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (2, 200,45,'B',6,'01/23/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (2, 250,50,'C',4,'01/24/2005')
select a.Rec_ID, a.Amt_Recieved, a.Amt_given,
b.Rec_Status, b.Rec_Frequency , b.Rec_date
from #temp a
inner join #temp b on a.Rec_Date=dateadd(day,-1,b.Rec_Date)
drop table #temp|||Thanks...Mark for the Reply..
There is a bit more to this the Previous day. The Previous date may not be
the immediate previous I am lloking for the maximum data previous to
Rec_date. Meaning if there is Record for 01/22 and 01/24 and not for 01/23
then we want data for 01/22
siaj
"markc600@.hotmail.com" wrote:

> set dateformat mdy
> create table #temp(Rec_ID int, Amt_Recieved int, Amt_given int,
> Rec_Status char(1), Rec_Frequency int, Rec_date
> smalldatetime)
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 100,50,'A',5,'01/21/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 200,60,'B',6,'01/22/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 300,70,'C',7,'01/23/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (2, 200,45,'B',6,'01/23/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (2, 250,50,'C',4,'01/24/2005')
> select a.Rec_ID, a.Amt_Recieved, a.Amt_given,
> b.Rec_Status, b.Rec_Frequency , b.Rec_date
> from #temp a
> inner join #temp b on a.Rec_Date=dateadd(day,-1,b.Rec_Date)
> drop table #temp
>|||Can you try this:
select T1.Rec_ID,
T1.Amt_Received,
T1.Amt_given,
T2.Rec_Status,
T2.Rec_Frequency ,
T2.Rec_date,
T2.Rec_date
from TryRec T1,
TryRec T2
where (T1.Rec_date < T2.Rec_date
and not exists (select *
from TryRec T3
where (T3.Rec_date > T1.Rec_date and T3.Rec_date <
T2.Rec_date)
or (T3.Rec_date = T2.Rec_date and T3.Rec_ID <
T2.Rec_ID)
)
and T1.Rec_ID = (select max(Rec_ID)
from TryRec T4
where T1.Rec_date = T4.Rec_date))
or (T1.Rec_date = T2.Rec_date
and T1.Rec_ID < T2.Rec_ID
and not exists (select *
from TryRec T5
where (T5.Rec_date = T1.Rec_date
and T5.Rec_ID > T1.Rec_ID
and T5.Rec_ID < T2.Rec_ID)
)
)
order by 1, 2
Perayu
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:E417D534-1B46-4957-B352-0E3CFA0ACEC3@.microsoft.com...
>I need a suggestion.
> I have a table with
> Columns
> (Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
> here
> Rec_ID and Rec_date are primary key
>
> I need a resultset out of this table which has columns Rec_ID,
> Amt_Recieved, Amt_given from the day previous to Rec_Date and Rec_Status,
> Rec_Frequency , Rec_date corresponds to the Rec_date
>
> For Eg.
> (Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
> 1, 100,50,A,5,01/21/2005
> 1, 200,60,B,6,01/22/2005
> 1, 300,70,C,7,01/23/2005
> 2, 200,45,B,6,01/23/2005
> 2, 250,50,C,4,01/24/2005
> I need out of it
> 1, 100,50,B,6,01/22/2005
> 1, 200,60,C,7,01/23/2005
> 1, 300,70,B,6,01/23/2005
> 2, 200,45,C,4,01/24/2005
>
> I have tried inline queries for each column but it takes a lot of time.
> Any advice shall be appreciated.
>
> Thanks,
> siaj|||thanks Much ...It helps..
siaj
"Perayu" wrote:

> Can you try this:
> select T1.Rec_ID,
> T1.Amt_Received,
> T1.Amt_given,
> T2.Rec_Status,
> T2.Rec_Frequency ,
> T2.Rec_date,
> T2.Rec_date
> from TryRec T1,
> TryRec T2
> where (T1.Rec_date < T2.Rec_date
> and not exists (select *
> from TryRec T3
> where (T3.Rec_date > T1.Rec_date and T3.Rec_date
<
> T2.Rec_date)
> or (T3.Rec_date = T2.Rec_date and T3.Rec_ID <
> T2.Rec_ID)
> )
> and T1.Rec_ID = (select max(Rec_ID)
> from TryRec T4
> where T1.Rec_date = T4.Rec_date))
> or (T1.Rec_date = T2.Rec_date
> and T1.Rec_ID < T2.Rec_ID
> and not exists (select *
> from TryRec T5
> where (T5.Rec_date = T1.Rec_date
> and T5.Rec_ID > T1.Rec_ID
> and T5.Rec_ID < T2.Rec_ID)
> )
> )
> order by 1, 2
> Perayu
>
> "siaj" <siaj@.discussions.microsoft.com> wrote in message
> news:E417D534-1B46-4957-B352-0E3CFA0ACEC3@.microsoft.com...
>
>sql

Query across all colums

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
Thanks
Shabassanot really|||Ok, thanks was worth a question.|||If you are comparing all the columns in one table to all the columns in another, you may be able to use the CHECKSUM or BINARYCHECKSUM functions:

select subA.APKey, sbuB.BPKey
from
(select A.PKey as APKey, CHECKSUM(*) as ChecksumA from A) subA
full outer join
(select B.PKey as BPKey, CHECKSUM(*) as ChecksumB from B) subB
on subA.ChecksumA = subB.ChecksumB
where ...|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:|||While Rudy is quite right in terms of how SQL itself does things, there are extensions to many database engines that make this kind of search easier. In MS-SQL, this is called Full Text Indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_5dro.asp).

-PatP|||pat, all things considered, full text indexing is not "simpler" (one of shabassa's original requirements)

:)|||From the standpoint of managing the server, you are quite right. Full-Text Indexing brings its own problems to the table.

From the standpoint of writing a search query though, I feel that it is simpler and I don't think anyone would argue it is much less code.

-PatP|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:

Could be. Without a doubt he needs to be more specific.|||I doubt it though....

I think they're looking for duplicates...

Or they're just trying to establish a join between to related tables, just in the keys?|||Or attempting to create a search feature for their application that searches against a catalog of objects where the entry could match a value in any number of fields (Ex: Name, Description, Price, etc).|||Step right up, folks! Play "Guess the user requirements" and win a prize for the little lady! Who's next? Every player has a chance to win! You sir, yes you...!|||Well if I was programming an application that searched across the table there would be no problem doing it in some kind of loop. I was just lazy and wanted to look for a certain row where I entered a date in an application accessing that db. But I didn't know to which colum the text field in the app corresponded to. So I wanted to search across all columns at once using the Enterprise Manager.
I hope that clears things up. ;)

How would this be possible with full text indexing? Do I magically get some kind of search field?

Shabassa

Query a Table to return Non-Date values

Hi
I have a table with dates stored in a nvarchar(75), I would like to move
this over to datetime (for obvious reasons)
the problem is the field has some 'bad data'
how can i query the nvarchar field to return me anything that isnt a date?
so i can fix and convert
Thanks in advance
MikeMike
Take a look at ISDATE () function
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
> Hi
> I have a table with dates stored in a nvarchar(75), I would like to move
> this over to datetime (for obvious reasons)
> the problem is the field has some 'bad data'
> how can i query the nvarchar field to return me anything that isnt a date?
> so i can fix and convert
> Thanks in advance
> Mike
>|||Uri,
I have been looking at that all morning and trying to understand it fully.
using the following query
SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
this returns a 1 or 0
the problem with ISDATE() is that it seems to rely on an american date
format
so 13/02/2003 is invalid whereas 02/13/2003 is valid
being in the UK the function appears to be as much use as a chocolate fire
guard :)
Thanks
Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Mike
> Take a look at ISDATE () function
>
> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>|||the UK problem can be fixed by using
SET DATEFORMAT dmy;
- got it working now
Thanks again
Mike
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:UpudnVw4esuFQG3anZ2dnUVZ8v6dnZ2d@.giganews.com...
> Uri,
> I have been looking at that all morning and trying to understand it fully.
> using the following query
> SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
> this returns a 1 or 0
> the problem with ISDATE() is that it seems to rely on an american date
> format
> so 13/02/2003 is invalid whereas 02/13/2003 is valid
> being in the UK the function appears to be as much use as a chocolate fire
> guard :)
> Thanks
> Mike
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Mike
>> Take a look at ISDATE () function
>>
>> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
>> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>>
>

query a table on different server instance

I have a table TA in database A on SQL server instance IA
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!
First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!

query a table on different server instance

I have a table TA in database A on SQL server instance IA
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!sql

Query a dynamic table...HELP!

Hello,

I am trying to write a query to pull data from a table that is system generated daily at midnight. I tried:

declare @.tablename varchar(50) set @.tablename = (select name from sysobjects where crdate = '05/01/2003') select * from @.tablename

But I get an error on 'select * from @.tablename' (must declare @.tablename)

Any assistance is GREATLY APPRECIATED!!!!Please check the answer in

http://forums.databasejournal.com/showthread.php?threadid=31446

Query 2 table in different databases

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

You need to use a 3-part name to reference the table from one database to another. The 3-part name consists of <database>.<schema>.<object>. The connection is only to the server and you can access any database to which you have permissions. You can either switch database context using USE <database> statement or use the 3-part name to reference objects.

Query ?

Here is the table:
CREATE TABLE [teamstats] (
[name] [varchar] (10) NOT NULL ,
[pos] [varchar] (3) NOT NULL ,
[ab] [numeric](3, 0) NOT NULL ,
[hits] [numeric](4, 0) NOT NULL ,
[walks] [varchar] (5) NOT NULL ,
[singles] [varchar] (7) NOT NULL ,
[doubles] [varchar] (7) NOT NULL ,
[triples] [varchar] (7) NOT NULL ,
[hr] [numeric](2, 0) NOT NULL ,
[so] [varchar] (2) NOT NULL
) ON [PRIMARY]
GO
When I run this:
select sum (singles) "total_singles", sum(doubles) "total_doubles",
sum (triples) "total_triples", sum (hr) "total_hr"
from teamstats
where hits/ab >=.300;
I get this:
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.
Server: Msg 409, Level 16, State 1, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.
Server: Msg 409, Level 16, State 1, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.The error that you receive is a pretty good pointer to the problem.
"The sum or average aggregate operation cannot take a varchar data type =
as an argument."
You cannot perform a SUM on a varchar data type. You need to convert =
the values to int before performing the SUM. This will work as long as =
you have numeric data stored within singles, doubles, and triples.
select sum (CONVERT(int,singles)) AS total_singles, =
sum(CONVERT(int,doubles)) AS total_doubles,
sum (CONVERT(int,triples)) AS total_triples, sum (hr) AS total_hr
from teamstats
where hits/ab >=3D.300
--=20
Keith
"Rmsands" <anonymous@.discussions.microsoft.com> wrote in message =
news:4B521924-DBB1-4262-B1E7-0988FB2F3632@.microsoft.com...
> Here is the table:
>=20
> CREATE TABLE [teamstats] (
> [name] [varchar] (10) NOT NULL ,
> [pos] [varchar] (3) NOT NULL ,
> [ab] [numeric](3, 0) NOT NULL ,
> [hits] [numeric](4, 0) NOT NULL ,
> [walks] [varchar] (5) NOT NULL ,
> [singles] [varchar] (7) NOT NULL ,
> [doubles] [varchar] (7) NOT NULL ,
> [triples] [varchar] (7) NOT NULL ,
> [hr] [numeric](2, 0) NOT NULL ,
> [so] [varchar] (2) NOT NULL=20
> ) ON [PRIMARY]
> GO
>=20
>=20
> When I run this:
>=20
> select sum (singles) "total_singles", sum(doubles) "total_doubles",
> sum (triples) "total_triples", sum (hr) "total_hr"
> from teamstats
> where hits/ab >=3D.300;
>=20
> I get this:
>=20
> Server: Msg 409, Level 16, State 2, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
> Server: Msg 409, Level 16, State 1, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
> Server: Msg 409, Level 16, State 1, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
>

Query / function ?

Query/Function Question
I have a retail issue where I store sizes in a table per item. The table
looks like:
PO #
Item #
Color
Size
Size Equiv
What I am trying to do is get a comma delimited list of sizes AND size Equiv
per po/item. The sizes AND Size Equiv need to be a unique list (group by)
excluding color
Example:
PO Item # Size Size Equiv
123 1 2, 4, 6, 8, 10 12, 14, 16, 18, 20
123 2 S, M, L, XL XS, S, M, L
Now the question:
I need to create a SQL query that builds the about list? Is there a way to
do a query call or must I use a stored proc curser to loop thru a select
query?
Thanks in advance,
Stewart Rogershttp://www.aspfaq.com/2529
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Datasort" <Datasort@.discussions.microsoft.com> wrote in message
news:1942D589-AD0C-44D9-B285-E6CB7682F208@.microsoft.com...
> Query/Function Question
> I have a retail issue where I store sizes in a table per item. The table
> looks like:
> PO #
> Item #
> Color
> Size
> Size Equiv
> What I am trying to do is get a comma delimited list of sizes AND size
Equiv
> per po/item. The sizes AND Size Equiv need to be a unique list (group by)
> excluding color
> Example:
> PO Item # Size Size Equiv
> 123 1 2, 4, 6, 8, 10 12, 14, 16, 18, 20
> 123 2 S, M, L, XL XS, S, M, L
> Now the question:
> I need to create a SQL query that builds the about list? Is there a way
to
> do a query call or must I use a stored proc curser to loop thru a select
> query?
>
> Thanks in advance,
> Stewart Rogers
>|||See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"Datasort" wrote:

> Query/Function Question
> I have a retail issue where I store sizes in a table per item. The table
> looks like:
> PO #
> Item #
> Color
> Size
> Size Equiv
> What I am trying to do is get a comma delimited list of sizes AND size Equ
iv
> per po/item. The sizes AND Size Equiv need to be a unique list (group by)
> excluding color
> Example:
> PO Item # Size Size Equiv
> 123 1 2, 4, 6, 8, 10 12, 14, 16, 18, 20
> 123 2 S, M, L, XL XS, S, M, L
> Now the question:
> I need to create a SQL query that builds the about list? Is there a way t
o
> do a query call or must I use a stored proc curser to loop thru a select
> query?
>
> Thanks in advance,
> Stewart Rogers
>|||If you want to get it done quickly and easily (minimal sql coding)
and don't care about the 'how', check out the RAC utility for S2k.
See the @.concatenate operator and you'll know what I mean:)
RAC and QALite @.
www.rac4sql.net
"Datasort" <Datasort@.discussions.microsoft.com> wrote in message
news:1942D589-AD0C-44D9-B285-E6CB7682F208@.microsoft.com...
> Query/Function Question
> I have a retail issue where I store sizes in a table per item. The table
> looks like:
> PO #
> Item #
> Color
> Size
> Size Equiv
> What I am trying to do is get a comma delimited list of sizes AND size
> Equiv
> per po/item. The sizes AND Size Equiv need to be a unique list (group by)
> excluding color
> Example:
> PO Item # Size Size Equiv
> 123 1 2, 4, 6, 8, 10 12, 14, 16, 18, 20
> 123 2 S, M, L, XL XS, S, M, L
> Now the question:
> I need to create a SQL query that builds the about list? Is there a way
> to
> do a query call or must I use a stored proc curser to loop thru a select
> query?
>
> Thanks in advance,
> Stewart Rogers
>sql

Query ... Distinct rows

I have a table as follows
ORDER_ID CODE STATUS
1000 XA3 5
1000 XA1 4
1000 XA7 5
1001 X35 5
1001 XA3 5
I want to run a query that will return the distinct ORDER_ID that is Status
= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
For example above, the result set will be 1001 only (as 1000 has one record
with Status of 4).
I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
appear to work :-(
Thanks in advance!
Wez
On Fri, 17 Jun 2005 03:30:02 -0700, Wez wrote:

> I have a table as follows
>ORDER_ID CODE STATUS
>1000 XA3 5
>1000 XA1 4
>1000 XA7 5
>1001 X35 5
>1001 XA3 5
>I want to run a query that will return the distinct ORDER_ID that is Status
>= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
>For example above, the result set will be 1001 only (as 1000 has one record
>with Status of 4).
>I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
>appear to work :-(
>Thanks in advance!
>Wez
Hi Wez,
This one should work, actually:
SELECT Order_ID
FROM YourTable
GROUP BY Order_ID
HAVING MIN(Status) = 5 AND MAX(Status) = 5
What eexactly does "doesn't appear to work" mean? Error messages? Wrong
results? Blue smoke in the server room? It's hard to help you without
knowing what's happening!
BTW, here's another query that should also work:
SELECT DISTINCT t1.Order_ID
FROM YourTable AS t1
WHERE NOT EXISTS (SELECT *
FROM YourTable AS t2
WHERE t2.Order_ID = t1.Order_ID
AND t2.Status <> 5)
/* Adding the line below might improve performance
AND t1.Status = 5
*/
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
when i say didnt work I mean I was getting wrong results i.e. orders
appearing in the result set that had lines not yet equal to status '5'.
However your alternative method has worked well :-)
Thanks,
Wez

Query "hightest value" contents of each "folder"?

Hi,

Can anyone tell me how to select the "most recent" date values from a
grouped query? Consider the following:

CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOT
NULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]

This is a simplified adjacency list. What I want to do is find the highest
valued item by date for each sub-tree. In other words, the single highest
date item when This.ID_Parent = That.ID_Parent. I think I first need to
group by ID_Parent, then select the TOP 1 from this query, but how to
aggregate them so I get the TOP 1 for each ID_Parent?

Thanks for any help you can give me,

RobinAnother way:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
WHERE T1.date =
(SELECT MAX(date)
FROM TestQuery AS T2
WHERE T1.id_parent = T2.id_parent)
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
LEFT JOIN TestQuery AS T2
ON T1.id_parent = T2.id_parent
AND T1.date < T2.date
WHERE T2.date IS NULL
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||here is a related article:
subject: select first record from a group ordered by 3 columns
date: Nov 27 2002, 5:01 am

http://groups-beta.google.com/group...23b16be2b945c0f

David Portas wrote:
> Another way:
> SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
> FROM TestQuery AS T1
> WHERE T1.date =
> (SELECT MAX(date)
> FROM TestQuery AS T2
> WHERE T1.id_parent = T2.id_parent)
> GROUP BY T1.id_parent, T1.date
> --
> David Portas
> SQL Server MVP
> --
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

query - which came last?

Hi,
Query puzzler here.
I have a table of PEOPLE.
Besides the Person ID, each record has five Date fields- Married ,
Graduated, Started Job, Got Famous, Got Rich
I want to write a query that shows what the current status of each person
is.
In other words, show which event has happened most recently for each person
Any help would be , and might keep me from getting fired.No datefields in the table? Can you show one or two rows as sample in your
table?
Thanks,
Sree
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Hi, no-one wants to see you get fired...
SELECT PersonID, max(Married) FROM (
select PersonID, Married from PEOPLE
union
select PersonID, Graduated from PEOPLE
union
select PersonID, [Started Job] from PEOPLE
union
select PersonID, [Got Famous] from PEOPLE
union
select PersonID, [Got Rich] from PEOPLE) as Derived
GROUP BY PersonID
HTH. Ryan
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||you can use this code:
SELECT ID,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then 'Married'
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then 'Graduated'
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then '[Started Job]'
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then '[Got Famous]'
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then '[Got Rich]'
Else 'nothing happend'
End
As LastEvent,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then Married
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then Graduated
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then [Started Job]
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then [Got Famous]
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then [Got Rich]
End
As LastEventDate
FROM PEOPLE
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Thanks - this seems to be the shorter of the two, saving typing, allowing
more time to beg for my job.
Both are instructive.
thnanks a lot
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||Hi this is almost there, but it returns dates. I need the column name
returened.
Like: Married
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||I need the status, not the date
OK I could use both.
I may get fired after all
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:

>Hi,
>Query puzzler here.
>I have a table of PEOPLE.
>Besides the Person ID, each record has five Date fields- Married ,
>Graduated, Started Job, Got Famous, Got Rich
>I want to write a query that shows what the current status of each person
>is.
>In other words, show which event has happened most recently for each person
>Any help would be , and might keep me from getting fired.
Hi Tales,
The person who designed that table should get fired, not you.
First, let's create a view to get a better normalised version of the
data:
CREATE VIEW Normalized
AS
SELECT PersonID, Married AS EventDate, 'Married' AS Event
FROM PEOPLE
UNION ALL
SELECT PersonID, Graduated, 'Graduated'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Started Job", 'Started Job'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Famous", 'Got Famous'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Rich", 'Got Rich'
FROM PEOPLE
go
With this, getting the required information becomes a lot easier:
SELECT a.PersonID, a.EventDate, a.Event
FROM Normalized AS a
WHERE a.EventDate = (SELECT MAX(b.EventDate)
FROM Normalized AS b
WHERE b.PeopleID = a.PeopleID)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||thanks a lot|||Hugo,
Is it just me, or are the columns Married, Graduated, StartedJob, GotFamous,
and GotRich unlikely columns in any database?
Unlikely, of course, unless the database is academic only.
Maybe Tales simply made up these column names because they couldn't tell us
the actual columns for proprietary reasons, but I am willing to bet this is
a classroom example, and no one's job is at risk.
I believe Tales just got this newsgroup to do his/her homework.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:r3adt15gapiv1bvvkcitvvrfq33rg850d8@.
4ax.com...
> On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:
> Hi Tales,
> The person who designed that table should get fired, not you.
>

query using the record number

happy friday...
my table has 200,000 + records and I want to see the particular record which I think it is causing the problem.
How could i query 195,203rd record?
thank you, yanorooWhat problem is it causing? How did you figure out the number to begin with?|||What problem is it causing? How did you figure out the number to begin with?

oh, the record number is just an example.
actual case is -I ran dts package to import data from txt file to sql table.
The dts truncated the table and imported the records correctly (218503 record) but I can't find the data from 208775th record on the table...
I want to see the what was populated into the table from 208775th record.
And strange thing is the table has 237043 record (which is more than input)|||are you getting 237043 by doing a count(*) on the table?|||are you getting 237043 by doing a count(*) on the table?
yes, it is by count(*) on the table.|||If you are just debugging the process, then temporarily create an incrementing identity column on the table.|||If you are just debugging the process, then temporarily create an incrementing identity column on the table.

Hello,

Found out the problem was DTS prod installation error.
Thank you anyway for your suggestion.
Yanoroo|||i suggest in a situation like this to create a staging table when you transfer large amounts of data to an sql server. this way you can set the error count high and allow sql server to leave the rows causing the error in the staging table while you move the clean data to the final destination. you can then have the dts send you an email notifying you of the prob and you can clean those rows in the morning.

go to www.SQLDts.com|||I assume there is an unique field named pid in your table and you are looking for a particular record of pid(in this case 195,203rd).You can try this query--

select * from mytable where pid not in
(select top 195,202 pid from mytable)

If you have any other type of requirement that can be solved too.
Subhasish

query - specify multiple items

Hi,
I am trying to develop a report where the user is able to specify the
information to be pulled from one specific field.
For example... the table has 2 fields... Company ID, Company Name
I only want to see information where Company ID = ABC and XYZ
but I want the user to be able to specify multiple Company ID's in the
report parameter
Thanks,
JenI've got part of the query figured out:
SELECT Company ID, Company Name
FROM Company Profile
WHERE (Company ID (@.CompanyID))
But this only lets me query one company id at a time. how can I alter the
query to allow users to query multiple company id's?
thanks
jen
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:O6rTcrxZHHA.1300@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am trying to develop a report where the user is able to specify the
> information to be pulled from one specific field.
> For example... the table has 2 fields... Company ID, Company Name
> I only want to see information where Company ID = ABC and XYZ
> but I want the user to be able to specify multiple Company ID's in the
> report parameter
> Thanks,
> Jen
>|||Hello Jennifer,
If you are using the SQL Server 2005, you could use the Multi-value
Parameter.
When you enable the Multi-value for the parameter, you could get a dropdown
list in the report.
Then you need to use the IN statement to query the data.
You could refer this article:
Adding a Multivalue Parameter
http://msdn2.microsoft.com/en-us/aa337396.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||We're currently running sql server 2000. i am using visual studio .net to
build the reports.
The way the query is build now, it allows the user to enter the parameters
in a seperate text box for each company id they query. how can i build the
query so that they can just enter a string of unlimited company id's?
thanks!
jen
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:OO42Wm4ZHHA.1784@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jennifer,
> If you are using the SQL Server 2005, you could use the Multi-value
> Parameter.
> When you enable the Multi-value for the parameter, you could get a
dropdown
> list in the report.
> Then you need to use the IN statement to query the data.
> You could refer this article:
> Adding a Multivalue Parameter
> http://msdn2.microsoft.com/en-us/aa337396.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
>
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Jen,
You could use the parameter as the String and use the comma mark to
seperate each company id.
And in the query, you could use the parameter like this:
Select * from tbl_companies where companyid in (@.Parameters)
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||hi wei,
thanks so much for checking in. everything worked out great :)
jen
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:5JytuQ7aHHA.1028@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hello Jen,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Query - perhaps GROUP BY

Hi,
Using SQL Server 2000
I have a table like this
Results
ResultID int primary key
ProductID char(10)
RetailerID int
RetailerPrice money
Some sample data might be
ResultID ProductID RetailerID RetailerPrice
1 1231231234 1 9.99
2 1231231234 2 19.99
3 1231231234 3 12.99
4 1231231235 1 11.99
5 1231231235 2 13.99
6 1231231235 3 3.99
I want to return the lowest price and it's resultid for a list of products.
I've got as far as
SELECT ProductID, MIN(RetailerPrice)
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID
which returns
ProductID RetailerPrice
1231231235 3.99
1231231234 9.99
What I want is
ProductID RetailerPrice ResultID
1231231235 3.99 6
1231231234 9.99 1
I've tried
SELECT ProductID, MIN(RetailerPrice), ResultID
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID, ResultID
but this returns every price for each product. I've tried various group by
clauses but have hit a brick wall. If anyone can point me in the right
direction I'd appreciate it.
Cheers,
JonLooks like a derived table:
SELECT
R.*
FROM
Results R
JOIN
(
SELECT ProductID, MIN(RetailerPrice) AS RetailerPrice
FROM Results
GROUP BY ProductID
) AS X ON X.ProductID = R.ProductID
AND X.RetailerPrice = R.RetailerPrice
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jon Spivey" <jons@.mvps.org> wrote in message
news:Ow$0XWdgGHA.4712@.TK2MSFTNGP05.phx.gbl...
Hi,
Using SQL Server 2000
I have a table like this
Results
ResultID int primary key
ProductID char(10)
RetailerID int
RetailerPrice money
Some sample data might be
ResultID ProductID RetailerID RetailerPrice
1 1231231234 1 9.99
2 1231231234 2 19.99
3 1231231234 3 12.99
4 1231231235 1 11.99
5 1231231235 2 13.99
6 1231231235 3 3.99
I want to return the lowest price and it's resultid for a list of products.
I've got as far as
SELECT ProductID, MIN(RetailerPrice)
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID
which returns
ProductID RetailerPrice
1231231235 3.99
1231231234 9.99
What I want is
ProductID RetailerPrice ResultID
1231231235 3.99 6
1231231234 9.99 1
I've tried
SELECT ProductID, MIN(RetailerPrice), ResultID
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID, ResultID
but this returns every price for each product. I've tried various group by
clauses but have hit a brick wall. If anyone can point me in the right
direction I'd appreciate it.
Cheers,
Jon|||Perfect. Thanks Tom.
Jon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23WI8MbdgGHA.3900@.TK2MSFTNGP05.phx.gbl...
> Looks like a derived table:
> SELECT
> R.*
> FROM
> Results R
> JOIN
> (
> SELECT ProductID, MIN(RetailerPrice) AS RetailerPrice
> FROM Results
> GROUP BY ProductID
> ) AS X ON X.ProductID = R.ProductID
> AND X.RetailerPrice = R.RetailerPrice
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Jon Spivey" <jons@.mvps.org> wrote in message
> news:Ow$0XWdgGHA.4712@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Using SQL Server 2000
> I have a table like this
> Results
> ResultID int primary key
> ProductID char(10)
> RetailerID int
> RetailerPrice money
> Some sample data might be
> ResultID ProductID RetailerID RetailerPrice
> 1 1231231234 1 9.99
> 2 1231231234 2 19.99
> 3 1231231234 3 12.99
> 4 1231231235 1 11.99
> 5 1231231235 2 13.99
> 6 1231231235 3 3.99
> I want to return the lowest price and it's resultid for a list of
> products.
> I've got as far as
> SELECT ProductID, MIN(RetailerPrice)
> FROM Results
> WHERE ProductID IN('1231231234', '1231231235')
> GROUP BY ProductID
> which returns
> ProductID RetailerPrice
> 1231231235 3.99
> 1231231234 9.99
> What I want is
> ProductID RetailerPrice ResultID
> 1231231235 3.99 6
> 1231231234 9.99 1
> I've tried
> SELECT ProductID, MIN(RetailerPrice), ResultID
> FROM Results
> WHERE ProductID IN('1231231234', '1231231235')
> GROUP BY ProductID, ResultID
> but this returns every price for each product. I've tried various group by
> clauses but have hit a brick wall. If anyone can point me in the right
> direction I'd appreciate it.
> Cheers,
> Jon
>

Query - need help using the IN function/statement

Hello all,
I am trying to find the instances in a field containg specific keywords or
strings of information. My table name is History, and my field name is
Notes. So what I am trying to do is find every record where History.Notess
conatins;
'chrom' or 'cell' or 'lab'
I think I need the IN functin as opposed to using a bunch of OR statements.
The OR statments work, but there are so many different keywords/strings,
that it is a real mess to enter all of the information.
Thank you for your help
JohnJohn,
You can put all the keywords in a table variable / temporary table /
permanent table a use:
select distinct notes
from history as h inner join t1 on h.notes like '%' + t1.keyword + '%'
Example:
use northwind
go
create table t1 (
c1 varchar(255)
)
go
create table t2 (
keyword varchar(25) not null unique
)
go
insert into t1 values('microsoft')
insert into t1 values('oracle')
insert into t1 values('microfocus')
go
insert into t2 values('micro')
insert into t2 values('of')
go
select distinct
t1.c1
from
t1
inner join
t2
on t1.c1 like '%' + t2.keyword + '%'
go
drop table t1, t2
go
Column [notes] can not be of type text / ntext.
AMB
"John Lloyd" wrote:

> Hello all,
> I am trying to find the instances in a field containg specific keywords or
> strings of information. My table name is History, and my field name is
> Notes. So what I am trying to do is find every record where History.Notes
s
> conatins;
> 'chrom' or 'cell' or 'lab'
> I think I need the IN functin as opposed to using a bunch of OR statements
.
> The OR statments work, but there are so many different keywords/strings,
> that it is a real mess to enter all of the information.
> Thank you for your help
> John
>sql

Query - House and Persons

I've got a a table House and a table Person
A house is contains 1 or 2 persons
If a house contains 2 persons the first person must be the one
alphabetically smaller

It gives me this query :

select *
from Person as FirstPerson
join (select IDHouse,IDPerson,Name From Person) as SecondPerson on FirstPerson.IDHouse = Second.IDHouse
join (select IDFoyer,NumberOfPersons from House) as TheHouse on FirstPerson.IDFoyer = TheHouse.IDHouse
where
(TheHouse.NumberOfPersons = '2' and
(FirstPerson.Name < SecondPerson.Name and FirstPerson.IDPerson <> SecondPerson.IDPerson) or
(First.Name = SecondPerson.Name and FirstPerson.IDPerson > SecondPerson.IDPerson)) or
(TheHouse.NumberOfPersons = '1')

How can I can it better ?The result must be

FirstPerson.Name, SecondPerson.Name, TheHouse.IDHouse

one row for each House|||briing up mey thread|||select min(FirstPersons.Name) FirstPerson,
min(SecondPersons.Name) SecondPerson,
FirstPersons.HouseID
from Persons FirstPersons
left outer join Persons SecondPersons
on FirstPersons.IDHouse = SecondPersons.IDHouse
and SecondPersons.Name > FirstPersons.Name
group by FirstPersons.HouseID

You will need to link in the house table if you want to include houses with no people in them.

blindman|||What about this?

create table house(id int)
go
create table person(id int,houseid int,pname varchar(10))
go
insert house values(1)
insert house values(2)
insert house values(3)
insert person values(1,1,'a')
insert person values(2,1,'b')
insert person values(3,2,'c')
insert person values(4,3,'d')
insert person values(5,3,'e')
go

select (select top 1 pname from person where houseid=h.id order by pname) as fperson
,isnull((select top 1 pname from person where houseid=h.id
and id not in (select top 1 id from person where houseid=h.id order by pname)),'nobody') as sperson
,id
from house h|||Exactly. A "house" is a thing and a "person" is a thing and a "person" occupies no more than one "house."

You don't want a number_of_persons field in the "house" table nor do you want any field in that table named "occupant_id."

A more generalized schema would have a table such as occupancy which lists house_id and person_id to reflect a "many to many" relationship between people and houses. This would also allow you to reflect the history of occupancy as a person moves. The question is, what does your application require. Outside of college textbooks, there are no graven slabs of stone being handed down from any mountainside...|||I've been tricked, I've simplified too much the problem

I can't use (Select top 1), Group by or Min() because I have about 20 columns in the Select
But I've kept the idea of adding a clause on a Left Outer Join

Select First.LastName,First.FirstName,Second.ClientType,T heHouse.City,...
From Person as First
Left outer join (Select IDPerson,FirstName,LastName,IDHouse From Person) as Second
On First.IDHouse = Second.IDHouse and
(First.LastName + First.FirstName + First.IDPerson <
Second.LastName + Second.FirstName + Second.IDPerson)
Join (Select IDHouse,City,NbPersons From House) as TheHouse
On First.IDHouse = TheHouse.IDHouse
Where
(TheHouse.NbOfPersons = '2' and Second.IDPers is not null) Or
(TheHouse.NbOfPersons = '1')

But it seems that replacing the <Inner Join> by a <Left outer join>
to select the second person gives me slower stats

Why ?|||An inner join is faster, but wil exclude houses with only one resident.

blindman|||Will a composite index on
<LastName + FirstName + IDPerson>

be faster than separate indexes on those columns

?|||Of course it will!|||I've put an composite index on those columns but
the query doesn't seem to need or use it

When I look at the "plan" of the query there is no mention of
the composite index

I've created this index by selecting all three columns
and naming it VousConjoint

I this Ok and why there is no use of my index ?|||Of course it won't!

Placing a composite index on the LastName, FirstName, and IDPerson fields won't help because you are actually joining on the concatenated of these three strings. This is not a good idea, because not only is it slow and unable to take advantage of a composite index, but the sorting logic can give erroneous results if two people have similiar last names. For instance, someone named John Brown should come before someone name Abe Browning in an alphabetical sort by last name, but the reverse occurs when sorting on the concatenated strings "BrownJohn" and "BrowningAbe".

To be accurate you should sort and compare on the values separately.

blindman

P.S. Adding a comma between your concatenated fields may fix the problem in your logic:

Where (First.LastName + ',' + First.FirstName + ',' + First.IDPerson < Second.LastName + ',' + Second.FirstName + ',' + Second.IDPerson)|||Try this strategy:

Add a calculated field to your Persons table that concatenates your LastName + ',' + FirstName + ',' + PersonID, and then index that field. Then this query should get you the information you want:

select House.*, Person1.*, Person2.*
from House
inner join
(select IDHouse,
Min(PrimaryResidents.FullName) PrimaryFullName,
Min(SecondaryResidents.FullName) SecondaryFullName
from Persons PrimaryResidents
left outer join Persons SecondaryResidents
on PrimaryResidents.HouseID = SecondaryResidents.HouseID
and PrimaryResidents.FullName < SecondaryResidents.FullName) Residents
on House.HouseID = Residents.HouseID
inner join Persons Person1 on Residents.PrimaryFullName = Person1.FullName
left outer join Persons Person2 on Residents.SecondaryFullName = Person2.FullName

blindman|||the calculated field must be a new column in the table person ?|||Yes.|||than I can't the structure of the table doesn't depend on me
too much programs are related to the SQL Server database
and there's also the replication + the big DB2 database structure

each time I demand a modification on the structure
the boss begins hillbilly talking|||Adding a calculated field should not be a big deal. It's not much different than adding an index.

Another alternative might be to create an indexed view that contains your concatenated values. There are restrictions on what types of views can be indexed, but I think yours would qualify. Theoretically you would not even need to reference the view in your query. The optimizer should know about it's existence and include it as a resource automatically.

Otherwise, run the query without the index and tell your boss why his policies result in slow execution times. Try to use short, simple words and phrases so that he will understand.

blindman|||i've read one indexes on views and tried it with no satisfying results
but i'm going to re-read BOL to see if i've missed something

just for info this is a part of the real query
SYBASE Version

=======================================
select '' as Prescripteur,
String(LeFoyer.BLOQUE) as ServiceClientBloquant,'0' as Entite,
String(LeFoyer.IDFoyer) as IDFoyer,
LeFoyer.SSSECTR as SStr,
LeFoyer.PTFAPC as PTFAPC,
LeFoyer.PersPTF as PersPTFFoyer,
LeFoyer.Voie,
LeFoyer.CpltAdr as ComplementAdresse,
LeFoyer.CDPOST as CodePostal,
LeFoyer.BurDist as Commune,
Trim(LeFoyer.Commune) as BureauDistributeur,
TypeEntite(LeFoyer.Typclpr) as TypeFoyer,
String(Premiere.IDPers) as IDVous,
Trim(Premiere.Nom) as NomVous,
Trim(Premiere.Prenom) as PrenomVous,
Premiere.Typclpr as TypeVous,
Premiere.PersPTF as PersPTFVous,
Premiere.MarchEntr as MarcheVous,
Conjoint(LeFoyer.NbPersPrinc,String(Deuxieme.IDPer s)) as IDConjoint,
Conjoint(LeFoyer.NbPersPrinc,Trim(Deuxieme.Nom)) as NomConjoint,
Conjoint(LeFoyer.NbPersPrinc,Trim(Deuxieme.Prenom) ) as PrenomConjoint,
Conjoint(LeFoyer.NbPersPrinc,Deuxieme.Typclpr) as TypeConjoint,
Conjoint(LeFoyer.NbPersPrinc,Deuxieme.PersPTF) as PersPTFConjoint,
Conjoint(LeFoyer.NbPersPrinc,Deuxieme.MarchEntr) as MarcheConjoint,
(if LeFoyer.NbPersPrinc = '001' then NomComplet(NomVous,PrenomVous) else NomComplet(NomVous,PrenomVous) || ' / ' ||
NomComplet(NomConjoint,PrenomConjoint) endif) as Foyer,
(if MarcheVous = '1' or MarcheConjoint = '1' then 1 else 0 endif) as MarcheFoyer,
(if Premiere.TYPCLPR = '04' then null else Premiere.DTNPAI endif) as DTNPAIVous,
(if LeFoyer.NbPersPrinc = '001' then null else(if Deuxieme.TYPCLPR = '04' then null else Deuxieme.DTNPAI endif) endif) as DTNPAIConjoint,
(if IsNull(LeFoyer.BurDist,'',LeFoyer.BurDist) = IsNull(Premiere.BurDist,'',Premiere.BurDist) and IsNull(LeFoyer.Voie,'',LeFoyer.Voie) =
IsNull(Premiere.Voie,'',Premiere.Voie) and IsNull(LeFoyer.Commune,'',LeFoyer.Commune) = IsNull(Premiere.Commune,'',Premiere.Commune) and
IsNull(LeFoyer.CpltAdr,'',LeFoyer.CpltAdr) = IsNull(Premiere.CpltAdr,'',Premiere.CpltAdr) and IsNull(LeFoyer.CdPost,'',LeFoyer.CdPost) =
IsNull(Premiere.CdPost,'',Premiere.CdPost) then DTNPAIVous else null endif) as NPAIParVous,
(if IsNull(LeFoyer.BurDist,'',LeFoyer.BurDist) = IsNull(Deuxieme.BurDist,'',Deuxieme.BurDist) and IsNull(LeFoyer.Voie,'',LeFoyer.Voie) =
IsNull(Deuxieme.Voie,'',Deuxieme.Voie) and IsNull(LeFoyer.Commune,'',LeFoyer.Commune) = IsNull(Deuxieme.Commune,'',Deuxieme.Commune) and
IsNull(LeFoyer.CpltAdr,'',LeFoyer.CpltAdr) = IsNull(Deuxieme.CpltAdr,'',Deuxieme.CpltAdr) and IsNull(LeFoyer.CdPost,'',LeFoyer.CdPost) =
IsNull(Deuxieme.CdPost,'',Deuxieme.CdPost) then DTNPAIConjoint else null endif) as NPAIParConjoint,
(if NPAIParVous is null then(if NPAIParConjoint is null then null else NPAIParConjoint endif)
else(if NPAIParConjoint is null then NPAIParVous else(if NPAIParVous > NPAIParConjoint then
NPAIParVous else NPAIParConjoint endif) endif)
endif) as NPAI,
LeFoyer.TYPCLPR,LeFoyer.PERSPTF,LeFoyer.SSSECTR,Le Foyer.CODDEV,LeFoyer.MNTRVFR,LeFoyer.MNTRVEU,LeFoy er.ORIGINE,LeFoyer.NBENFNT,
LeFoyer.NM1RCMDT,LeFoyer.NM2RCMDT,LeFoyer.PN1RCMDT ,LeFoyer.PN2RCMDT from
dbo.V74PERS0 as Premiere join
(select IDPers,Nom,Prenom,Qualite,Typclpr,IDFoyer,PERSPTF, (if TYPCLPR = '04' then null else DTNPAI endif) as DTNPAI,Voie,Commune,Burdist,CpltAdr,
CdPost,MARCHENTR from dbo.V74PERS0 where Typpers || Suptechn = '10') as Deuxieme(IDPers,Nom,Prenom,Qualite,Typclpr,IDFoyer ,PERSPTF,DTNPAI,Voie,
Commune,Burdist,CpltAdr,CdPost,MARCHENTR) on Premiere.IDFoyer = Deuxieme.IDFoyer join
(select TypSup,IDFoyer,Voie,Commune,BurDist,CDPOST,Typclpr ,CpltAdr,PersPTF,SSSECTR,NBPERSPRINC,BLOQUE,PTFAPC ,CODDEV,MNTRVFR,MNTRVEU,ORIGINE,NBENFNT,NM1RCMDT,
NM2RCMDT,PN1RCMDT,PN2RCMDT from dbo.V74FOYE0) as LeFoyer(TypSup,IDFoyer,Voie,Commune,BurDist,CDPOST ,Typclpr,CpltAdr,PersPTF,SSSECTR,
NBPERSPRINC,BLOQUE,PTFAPC,CODDEV,MNTRVFR,MNTRVEU,O RIGINE,NBENFNT,NM1RCMDT,NM2RCMDT,PN1RCMDT,PN2RCMDT ) on Premiere.IDFoyer = LeFoyer.IDFoyer where
Premiere.Typpers || Premiere.Suptechn = '10' and
((LeFoyer.NbPersPrinc || LeFoyer.TypSup = '002 ' and
(Premiere.TYPCLPR || Premiere.PERSPtf || Premiere.Qualite || Premiere.Nom || Premiere.Prenom <
Deuxieme.TYPCLPR || Deuxieme.PERSPtf || Deuxieme.Qualite || Deuxieme.Nom || Deuxieme.Prenom and Premiere.IDPers <> Deuxieme.IDPers) or
(Premiere.TYPCLPR || Premiere.PERSPtf || Premiere.Qualite || Premiere.Nom || Premiere.Prenom =
Deuxieme.TYPCLPR || Deuxieme.PERSPtf || Deuxieme.Qualite || Deuxieme.Nom || Deuxieme.Prenom and Premiere.IDPers > Deuxieme.IDPers)) or
(LeFoyer.NbPersPrinc || LeFoyer.TypSup = '001 '))|||it's horrible 'cause i've got to calculate lot's of fields
before connecting my Grid "via" Ado-ODBC
in my program.

i'm a bit sick of this query
- gaaah - i'm in agony
going home...

thx for your help Mr US-Ohio-BlindMan|||It still will be faster if you put IDHouse in front of LastName, because the join starts with it.|||rdjabarov

what do you mean, "IDHouse in front of LastName"

Query - All from one Table and 'TOP' from another

Hi - Have query structure language question
Hi have two tables,
Table A has a picklist for a field of limited options - say two, X, Y or Z
Table B 'looks up' to those values, so new entrys, must be either X, Y or Z,
but other data is entered, such as a date. So over time there will be many
entries with the linked field being either X, Y or Z, but with a timeline of
dates.
Want I want is a query that will return results for every item in the
picklist (in this case three, X,Y or Z and only the top value (most recent
date) for the date field in Table B.
(FYI - another Table C, links to table A where other fields must be equal.
Thus, what I hope to achieve is that, an entry in the Table C will be
assigned a link to table A, which will inturn return the most up to date
value in Table B.)
Hope someone can help. Ask if you need more detail.
Jon
Jonathan wrote:
> Hi - Have query structure language question
Unfortunately you didn't post any SQL. Instead you posted a word
puzzle. So we have to work out what you meant before we can answer you.
Posting some simple CREATE TABLE statements is a much better way to
describe a problem and it would have saved you some typing too :-)

> Hi have two tables,
> Table A has a picklist for a field of limited options - say two, X, Y or Z
> Table B 'looks up' to those values, so new entrys, must be either X, Y or Z,
> but other data is entered, such as a date. So over time there will be many
> entries with the linked field being either X, Y or Z, but with a timeline of
> dates.
> Want I want is a query that will return results for every item in the
> picklist (in this case three, X,Y or Z and only the top value (most recent
> date) for the date field in Table B.
> (FYI - another Table C, links to table A where other fields must be equal.
> Thus, what I hope to achieve is that, an entry in the Table C will be
> assigned a link to table A, which will inturn return the most up to date
> value in Table B.)
>
Here's my guess:
CREATE TABLE B (x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, dt
DATETIME NOT NULL, PRIMARY KEY (x,y,z,dt /* ? */));
/* Latest date for each x,y,z */
SELECT x,y,z, MAX(dt) AS dt
FROM B
GROUP BY x,y,z ;

> Hope someone can help. Ask if you need more detail.
DDL with keys and other constraints. Sample data (INSERT statements)
and show what end result you want based on that sample data. Also tell
us what version of SQL Server you have.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

query (deals with exclusions)

I have 2 tables. The first table is a master table with 3 fields: record id, list, and value. The second table is a lookup table that has the list and value in it, as well as fieldname. I need a query that will return a count of records in the master table that do not exist in the lookup table based on list and value. It seems straightforward but my brain doesn't seem to be working. I keep returning the count of records that don't match in the lookup table, instead of the master table. can anyone help? This was my code that isn't working:

select m.list, m.value, count(*)
from master m, lookup l
where m.list = l.list and
m.value <> l.value
and fieldname = 'BOC'
group by m.list, m.value

Thanks in advance.If the columns LIST and VALUE are the joining columns between the MASTER table and the LOOKUP table then try this query:

SELECT COUNT(*)
FROM MASTER m
WHERE NOT EXISTS
(
SELECT *
FROM LOOKUP l
WHERE l.LIST = m.LIST
AND l.VALUE = m.VALUE
)

Query

Hi,

I have a table called Strategic Programmes which has 2 columns "ProgNo" (unique ID) and "ProgName"

I need to connect to SQL Server from Access using connection and recordset objects to find the next highest "ProgNo"

What is the SQL query I need to execute to find the next highest ID number and how do I extract this from my recordset?

Thanks,

Mark

Track:

This query will work both for SQL Server 2000 and SQL Server 2005:

select max (progNo)
from ( select top 2
progNo
from [Strategic Programmes]
order by progNo
) a

Note that it will perform better if the table is either keyed or indexed on the progNo column.

|||

If you are looking for "second" highest, above solutions works perfect.

But if you are looking for "next" highest, following is my way:

select
nextHighestProgNo = max(ProgNo)
from
YourTable
where
ProgNo < CurrentProgNo

If you are currennt ProgNo is 15 and if you execute above query by replaceing CurrentProgNo with 15, you will get the next highest ProgNo. (will be 14 if there is one)

sql