Query/View Question

I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.

The tables are basically set up like this:

TABLE 1

PrimaryKey

Textfield1

Textfield2

Textfield3

TABLE 2

PrimaryKey

Table1ForeignKey

Table3ForeignKey

Textfield1

TABLE 3

PrimaryKey

Textfield1

Textfield2

Textfield3

Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.

I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.

From Table 2 I only want the latest record for each record in Table 1.

I want the view to look something like this:

Table 1

PrimaryKey

Table1

Textfield1

Table2

Textfield

Table3

Textfield

In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.

Can anyone enlighten me on the query necessary to get this view?

Hi,

some more questions:

how do you define "the latest" in table2 ?

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de

|||Since the Primary Key field autoincrements, the 'latest' record from Table 2 will always be the max(table2.primarykey).|||

Perhaps my question will make more sense explained like this:

I will use an analogy of checking out books from the library.

Table 1 is a table of books, with a primary key of bookid.

Table 2 is a detail record of who withdrew the book, when, when it was returned, etc. with a primary key of DetailID and has a foreign key to Table 1 to identify the book as well as a foreign key to table 3 to identify who withdrew it.

Table 3 is a table of library card holders contact info with a primary key of CardholderID.

All of the primary keys are auto-incrementing.

I want the view to basically give me a snapshot of ALL books, and if it a particular book is currently withdrawn, I want to see who has it and when they checked it out.

I hope that makes more sense.

|||

OK, keeping your analogy in mind, the query should be like:

Select
T1.PrimaryKey,T1.TextField,
T2.PrimaryKey,T2.TextField,
T3.TextField
FROM Table1 T1
LEFT JOIN
(
SELECT Table1FK, Table3FK,Textfield
FROM Table2
INNER JOIN
(
SELECT MAX(PrimaryKey) as PK, Table1PK
FROM TABLE2
GROUP BY Table1PK
) SubQuery
ON Subquery.PK = Table2.PK
AND SubQuery.Table1PK = Table2.Table1PK
) T2
ON
T1.PrimaryKey = T2.Table1FK
INNER JOIN Table3 T3
ON T3.PrimaryKey = T2.Table1FK

untested....

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use an
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquettXe.asp?id=5006
John
"Rossco" wrote:

> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below also).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))

Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also
).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
--
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use a
n
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquett_e.asp?id=5006
John
"Rossco" wrote:

> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below al
so).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6)))
AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6)))
AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))sql

Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
--
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use an
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquett­e.asp?id=5006
John
"Rossco" wrote:
> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below also).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))

Query which returns only every 5th Row with a Openquery

Hi @.all,
I'm going crazy here.
I need to modify this Query so that it returns only every 5th Row
because I have to generate Chart with that data and it would take to
long to get 50000 rows from the DB (at the moment it takes about 2
minutes only for retrieving the data).
The problem is that I have to use the Openquery command.
the command I use now is like that:
SELECT *
FROM OpenQuery( INSQL, '
SELECT DateTime, identifier,meter, x1, x2, x3, x4, x5 FROM WideHistory
WHERE DateTime >= "2006-05-29 12:00:00"
AND DateTime <= "2006-05-29 19:00:00" and identifier is not null')
I used to try some hints that I found here but they unfortunaly don't
work.
I tried the following which would be perfect for me but it end up with
full processor load for over 10 minutes. (I think the OLEDB provider
just crashed)
SELECT
Meter, x1, x2, x3, x4, x5 ISNULL(
(SELECT MIN(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1,
x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S3
WHERE S3.datetime >= S1.datetime
AND ISNULL(
DATEDIFF(
SECOND,
S3.datetime,
(SELECT MIN(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier,
meter, x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S4
WHERE S4.datetime > S3.datetime)), 10) <= 10),datetime)
AS endtime
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1, x2,
x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >= "2006-05-29
17:00:00" AND DateTime <= "2006-05-29 19:00:00" and identifier is
not null') AS S1
WHERE ISNULL(
DATEDIFF(
SECOND,
(SELECT MAX(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter,
x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S2
WHERE S2.datetime < S1.datetime),
S1.datetime),
10) <= 10)
Thanks for any help or hint.
Btw: I'm using C# for the frontendHi there,
here is example of the result from the above query:
DateTime identifier meters x1 x2 x3 x4
2006-05-29
17:00:00.000 rollcode901234filmcode901234ref<1678 41213.57421875 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.063 rollcode901234filmcode901234ref<1678 41216.11328125 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.267 rollcode901234filmcode901234ref<1678 41218.14453125 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.467 rollcode901234filmcode901234ref<1678 41218.14453125 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.563 rollcode901234filmcode901234ref<1678 41220.07421875 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.767 rollcode901234filmcode901234ref<1678 41222.20703125 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.967 rollcode901234filmcode901234ref<1678 41222.20703125 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.063 rollcode901234filmcode901234ref<1678 41224.64453125 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.280 rollcode901234filmcode901234ref<1678 41227.18359375 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.467 rollcode901234filmcode901234ref<1678 41227.18359375 236.0 237.0
238.0 239.0
2006-05-29
17:00:01.577 rollcode901234filmcode901234ref<1678 41229.11328125 236.0 237.0
238.0 239.0
2006-05-29
17:00:01.767 rollcode901234filmcode901234ref<1678 41231.75390625 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.063 rollcode901234filmcode901234ref<1678 41233.88671875 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.280 rollcode901234filmcode901234ref<1678 41236.52734375 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.467 rollcode901234filmcode901234ref<1678 41236.52734375 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.563 rollcode901234filmcode901234ref<1678 41238.86328125 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.767 rollcode901234filmcode901234ref<1678 41240.89453125 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.967 rollcode901234filmcode901234ref<1678 41240.89453125 239.0 240.0
241.0 242.0|||Where is the database that you are accessing via open query?
Ideally, you want to do as much of the processing as possible on the source
database. SQL Server can only do so much tuning when it is querying a table
from another database, and it gets much worse when you include more than one
remote table.
You may be much better off building this query on the remote database, or
selecting all the rows and letting C# filter so only every 5th row is kept.
<benwilliams269@.gmail.com> wrote in message
news:1149149017.711607.281380@.i40g2000cwc.googlegroups.com...
> Hi @.all,
> I'm going crazy here.
> I need to modify this Query so that it returns only every 5th Row
> because I have to generate Chart with that data and it would take to
> long to get 50000 rows from the DB (at the moment it takes about 2
> minutes only for retrieving the data).
> The problem is that I have to use the Openquery command.
> the command I use now is like that:
> SELECT *
> FROM OpenQuery( INSQL, '
> SELECT DateTime, identifier,meter, x1, x2, x3, x4, x5 FROM WideHistory
> WHERE DateTime >= "2006-05-29 12:00:00"
> AND DateTime <= "2006-05-29 19:00:00" and identifier is not null')
> I used to try some hints that I found here but they unfortunaly don't
> work.
> I tried the following which would be perfect for me but it end up with
> full processor load for over 10 minutes. (I think the OLEDB provider
> just crashed)
> SELECT
> Meter, x1, x2, x3, x4, x5 ISNULL(
> (SELECT MIN(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1,
> x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S3
> WHERE S3.datetime >= S1.datetime
> AND ISNULL(
> DATEDIFF(
> SECOND,
> S3.datetime,
> (SELECT MIN(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier,
> meter, x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S4
> WHERE S4.datetime > S3.datetime)), 10) <= 10),datetime)
> AS endtime
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1, x2,
> x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >= "2006-05-29
> 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and identifier is
> not null') AS S1
> WHERE ISNULL(
> DATEDIFF(
> SECOND,
> (SELECT MAX(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter,
> x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S2
> WHERE S2.datetime < S1.datetime),
> S1.datetime),
> 10) <= 10)
>
> Thanks for any help or hint.
> Btw: I'm using C# for the frontend
>|||Here is a rewrite of your query, set to run only against the original
database. See how long it takes to run when run directly against the
source. You may need to tweak some of the syntax, depending on what type of
database you are dealing with. I think the speed will be much, much better
this way.
However, if you want to get every 5th record, I suggest looking into various
paging/ranking techniques to assign row numbers and then retrieve every 5th
row. Try this link to start:
http://www.aspfaq.com/show.asp?id=2427
SELECT
S1.Meter
, S1.x1
, S1.x2
, S1.x3
, S1.x4
, S1.x5
, ISNULL
(
(
SELECT min(S3.datetime)
FROM Runtime.WideHistory AS S3
WHERE S3.DateTime >= "2006-05-29 17:00:00"
AND S3.DateTime <= "2006-05-29 19:00:00"
and S3.identifier is not null
and S3.datetime >= S1.datetime
AND ISNULL
(
DATEDIFF
(
SECOND,
S3.datetime,
(
SELECT MIN(S4.datetime) as datetime
FROM Runtime.WideHistory AS S4
WHERE S4.DateTime >= "2006-05-29 17:00:00"
AND S4.DateTime <= "2006-05-29 19:00:00"
and S4.identifier is not null
and S4.datetime > S3.datetime
)
), 10
) <= 10
),datetime
) AS endtime
FROM Runtime.WideHistory AS S1
WHERE S1.DateTime >= "2006-05-29 17:00:00"
AND S1.DateTime <= "2006-05-29 19:00:00"
and S1.identifier is not null
and ISNULL
(
DATEDIFF
(SECOND,
(
SELECT MAX(S2.datetime) as datetime
FROM Runtime.WideHistory AS S2
WHERE S2.DateTime >= "2006-05-29 17:00:00"
AND S2.DateTime <= "2006-05-29 19:00:00"
and S2.identifier is not null
and S2.datetime < S1.datetime
),S1.datetime
),10
)
<= 10
)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OlAWNyZhGHA.412@.TK2MSFTNGP05.phx.gbl...
> Where is the database that you are accessing via open query?
> Ideally, you want to do as much of the processing as possible on the
source
> database. SQL Server can only do so much tuning when it is querying a
table
> from another database, and it gets much worse when you include more than
one
> remote table.
> You may be much better off building this query on the remote database, or
> selecting all the rows and letting C# filter so only every 5th row is
kept.
>
> <benwilliams269@.gmail.com> wrote in message
> news:1149149017.711607.281380@.i40g2000cwc.googlegroups.com...
>

query to return only non null fields

Is it possible to write a query that returns only non null fields from a specified record? I have a big table with a record for each customer. the record contains a field for each item that can be purchased (only 6 items). I need to write an invoice but not every customer buys every product. I get the feeling Im going about this all wrong. Any help would be great

ThanksOriginally posted by nicky w
Is it possible to write a query that returns only non null fields from a specified record? I have a big table with a record for each customer. the record contains a field for each item that can be purchased (only 6 items). I need to write an invoice but not every customer buys every product. I get the feeling Im going about this all wrong. Any help would be great

Thanks
It would have been better to have the up to 6 items as up to 6 records in a separate table. No SQL query can return a variable number of columns, you would have to write some procedural code to run the query and then present the NOT NULL data.|||are you still in a designing stage? then you should change the design.
What if more items will be offered?

referential integrity will prevent "lost childs"

otherwise andrew is right. write some procedural code

Query to return latest record, multiple join fields

Hi
I need to write a query that returns the latest value(s) from a table,
'grouped' by the primary key (multiple fields), and the criteria to
derive the latest record is also based on multiple fields.
I have put together the DDL below as a simplified example, and want to
write a query that returns the following resultset:
company--project--value--
1 1 'fifth value'
1 2 '.2 fifth value'
2 1 '2 fifth value'
(KEY: company + project)
(LATEST RECORD: year + batch + item)
Thanks for any help
Sean
---
CREATE TABLE mytable (company INT, project INT, [year] int, batch int,
item int, value varchar(35))
INSERT INTO mytable VALUES (1, 1, 2003, 1, 1, 'first value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 2, 'second value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 3, 'third value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 1, 'fourth value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 2, 'fifth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 1, 'sixth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 2, 'seventh value')
INSERT INTO mytable VALUES (1, 1, 2002, 2, 1, 'eighth value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 1, '.2 first value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 2, '.2 second value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 3, '.2 third value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 1, '.2 fourth value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 2, '.2 fifth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 1, '.2 sixth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 2, '.2 seventh value')
INSERT INTO mytable VALUES (1, 2, 2002, 2, 1, '.2 eighth value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 1, '2 first value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 2, '2 second value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 3, '2 third value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 1, '2 fourth value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 2, '2 fifth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 1, '2 sixth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 2, '2 seventh value')
INSERT INTO mytable VALUES (2, 1, 2002, 2, 1, '2 eighth value')
---This table doesn't appear to have a primary key. I'll assume that the key is
supposed to be (company,project,year,batch,item). I've also assumed that the
batch and item numbers are in the range 0-999. If not, you'll have to amend
the YBI calculation accordingly.
SELECT T.company, T.project, T.value
FROM MyTable AS T
JOIN
(SELECT company, project,
MAX([year]*1000000+batch*1000+item) AS ybi
FROM Mytable
GROUP BY company, project) AS M
ON T.company = M.company
AND T.project = M.project
AND T.[year]*1000000+T.batch*1000+T.item = M.ybi
--
David Portas
--
Please reply only to the newsgroup
--sql

Query to return duplicate records

I have a table with a column varchar(50), say colA.
How can I create a sql query that returns all records with duplicate colA ?
For example:
colA colB
1 A
2 B
2 B
3 C
2 is the duplicate records for colA. How can I return those records ?
Thanks.SELECT ColA, count(*) FROM TableName
GROUP BY ColA
HAVING COUNT(*) > 1
HTH. Ryan
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>|||"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>
SELECT T.cola, T.colb
FROM your_table AS T
JOIN
(SELECT cola
FROM your_table
GROUP BY cola
HAVING COUNT(*)>1) AS D
ON T.cola = D.cola ;
David Portas
SQL Server MVP
--|||Select colA,ColB
>From Sometable
Where colA in
(
Select colA
From SomeTable
Group by colA
Having count(*) >1
)
HTH, jens Suessmeyer.

Query Timeout

Hello,
Our system has experienced time out issues with on of the SQL Severs. We
have an ASP.NET application that calls a stored proc, which returns a result
set. This stored proc all of a sudden started to time out on Friday after
noon. I was able to execute the same procedure in Query Analyzer without any
problems. The timeout for My ASP.NET is 30 seconds and the query was
executing the Query Analyzer in less than 30 seconds.
The solution I found out for this is to update statistics on one of the
databases. The stored proc has a join with a table in different database
sitting on the same server. When I updated the statistics of this database
the ASP.NET app started behave normally. This time I did not run the update
stats, instead I was thinking to do more research this morning. To my
surprise, the time out issue went away and ASP.NET is working absolutely
fine. What I found is that the SQL server might have been rebooted this
weekend.
Now I am scratching my head and trying to figure out what could be the
problem. What is it'
I appreciate any comments.
ThanksIf you have SQL Server configured to auto update stats, it may have done an
update itself which improved the stats and allowed it to create a good query
plan again.
My experience has been that auto update is not reliable enough, so we
schedule update statistics ... with fullscan against all tables across the
course of a week.
--
Scott Nichol
<Srini> wrote in message news:eRWM0U7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Our system has experienced time out issues with on of the SQL Severs. We
> have an ASP.NET application that calls a stored proc, which returns a
result
> set. This stored proc all of a sudden started to time out on Friday after
> noon. I was able to execute the same procedure in Query Analyzer without
any
> problems. The timeout for My ASP.NET is 30 seconds and the query was
> executing the Query Analyzer in less than 30 seconds.
>
> The solution I found out for this is to update statistics on one of the
> databases. The stored proc has a join with a table in different database
> sitting on the same server. When I updated the statistics of this database
> the ASP.NET app started behave normally. This time I did not run the
update
> stats, instead I was thinking to do more research this morning. To my
> surprise, the time out issue went away and ASP.NET is working absolutely
> fine. What I found is that the SQL server might have been rebooted this
> weekend.
>
> Now I am scratching my head and trying to figure out what could be the
> problem. What is it'
> I appreciate any comments.
>
> Thanks
>

Query syntax help

I am trying to write a query that returns all suppliers within a given range
that either do not have any insurance (appear only in Suppliers table) or
Suppliers where the insurance has expired from a given date
Eg 4 Suppliers
Supplier1 - no insurance
Supplier2 - insurance expired
Supplier3 - insurance current
Supplier4 - not in range
The Supplier range is 'where AccRef like '^SC%'
The Expiry Date is less than or equal to '20071130'
The result set would include Supplier1 because it is not in the
InsuranceDetails table at all and Supplier2 because the insurance has
expired.
How can I do this in one query?
I have included some SQL for your info
Thanks
A
CREATE TABLE [dbo].[Suppliers](
[AccRef] [nvarchar](8) NOT NULL,
[AccName] [nvarchar](30) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[InsuranceDetails](
[AccRef] [nvarchar](8) NOT NULL,
[DateExpire] [datetime] NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC100' As Expr1, 'Supplier1' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC200' AS Expr1, 'Supplier2' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC300' AS Expr1, 'Supplier3' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '10000' AS Expr1, 'Supplier4' as Expr2
INSERT INTO dbo.InsuranceDetails ([AccRef], [DateExpire])SELECT '^SC300' AS
Expr1, '20080331' as Expr2
INSERT INTO dbo.InsuranceDetails ([AccRef], [DateExpire])SELECT '^SC200' AS
Expr1, '20071101' as Expr2
There are a variety of ways you can do this. Here is one:
SELECT s1.accref, s1.AccName
FROM suppliers s1
WHERE s1.accref LIKE '^SC%'
AND COALESCE(
( SELECT i1.dateexpire
FROM InsuranceDetails i1
WHERE i1.accref = s1.accref ), '19000101' )
<= '20071130' ;
Anith

Query strings that dont start with a letter

I have a table in Sql Server 2000 called Users. The field in question here is "Username". I need to create a query that returns "Usernames" that starts with anything other than letters from the alphabet. For example: numerics, -, !, # and so on...

Does anyone know how to do this?

Thanks alot!Hi DrEaMON,
1.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp
LIKE statement syntax

2.
select UserName
from Users
where UserName like '[^A-Z]%'|||Thank you! That's exactly what I needed.

You learn something new every day huh!?!