Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
--
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann Blake
Hi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakePlease post proper DDL with keys and constraints so that we don't have to
guess. I'm guessing that (textid, languageid) is unique just as I'm guessing
that ID is an IDENTITY value and is therefore probably not useful in the
solution.
SELECT id, textid, languageid, categoryid, text
FROM YourTable AS T
WHERE languageid =
(SELECT
COALESCE(MIN(CASE WHEN languageid = 'EN' THEN 'EN' END),
MIN(languageid))
FROM YourTable
WHERE textid = T.textid) ;
(untested)
David Portas
SQL Server MVP
--|||Hi Johann
Your post is inconsistent check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data as insert statements. It is also useful to give expected output.
Ignoring the categoryid as there is no value of 3 then you may want somethin
g
similar to:
CREATE TABLE #MyTable ( ID int NOT NULL primary key,
TextID int,
LanguageID char(2),
CategoryID int,
Text nvarchar(260) )
INSERT INTO #Mytable (ID, TextID, LanguageID, CategoryID, [Text] )
SELECT 1 , 1 , 'en' , 1 , 'apple'
UNION ALL SELECT 2, 1, 'de', 1,
'Apfel'
UNION ALL SELECT 3, 2, 'fr', 1,
'Lundi'
UNION ALL SELECT 4, 2, 'de', 1,
'Montag'
UNION ALL SELECT 5, 3, 'en', 2,
'Food'
SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #Mytable M
WHERE LanguageID = 'en'
UNION ALL SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #MyTable M
WHERE NOT EXISTS ( SELECT * FROM #MyTable D WHERE D.TextId = M.TextId AND
D.LanguageID = 'en' )
AND id = ( SELECT MIN(id) FROM #MyTable E WHERE E.TextId = M.TextId )
John
"Johann Blake" wrote:

> I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||I believe by showing the example data that it is clear what is unique
and what isn't. Accordingly, the TextID and LanguageID fields are NOT
unique. Thanks for your example. I needed to add the AND section to
limit the records to the CategoryID. But it works! I'll need to read up
on the COALESCE since I've never come across that before.
Thanks again
Johann Blake|||> I believe by showing the example data that it is clear what is unique
> and what isn't. Accordingly, the TextID and LanguageID fields are NOT
> unique.
Non sequitur because in your example data that compound key IS unique.
Glad if I helped anyway.
David Portas
SQL Server MVP
--

need help

CREATE TABLE [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] ([Rec_Date_Tm] [datetime] NULL, [QCluster_Count_1_SA2] [int] NULL , [QCluster_Count_2_SA2] [int] NULL , [QCluster_Count_3_SA2] [int] NULL , [QCluster_Count_4_SA2] [int] NULL , [QCluster_Count_5_SA2] [int] NULL , [QCluster_Count_1_SB2] [int] NULL , [QCluster_Count_2_SB2] [int] NULL , [QCluster_Count_3_SB2] [int] NULL , [QCluster_Count_4_SB2] [int] NULL , [QCluster_Count_5_SB2] [int] NULL) ON [PRIMARY]

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] TO [irisaoidb]

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] ([lm0805] [varchar] (50) NULL, [rsc725] [varchar] (50) NULL) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] TO [irisaoidb]

INSERT INTO TP_Large_Void_Rawdata_794981854_2007319_10613_7944 SELECT Rec_Date_Tm, QCluster_Count_1_SA2, QCluster_Count_2_SA2, QCluster_Count_3_SA2, QCluster_Count_4_SA2, QCluster_Count_5_SA2, QCluster_Count_1_SB2, QCluster_Count_2_SB2, QCluster_Count_3_SB2, QCluster_Count_4_SB2, QCluster_Count_5_SB2 FROM tbl_IRISAOI_Rawdata_KM1 (nolock),TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944 (nolock) WHERE Rec_Date_Tm >= '2007-03-19 00:00:00' AND Rec_Date_Tm <= '2007-03-19 20:00:00' AND Testcell = TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944.Selected_Value AND Product = TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944.Selected_Value AND Lot_No = TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944.Selected_Value and tester in (ALL) and upper(Lot_Id_Code) in (ALL) and upper(Bin_No) in (ALL) and upper(Grade_No) in (ALL)

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'ALL'.

can anyone point out what mistake that i've made here?

The ALL is a parameter that passed to the stored procedure when invoked.

|||

oops, in fact i've left out the CHAR(39) on both side of the parameter...

now the problem is solved.

sql

Need a tip to generate Identity

Hi all,
I need the same functionality as IDENTITY(int,1,1) but only in a SELECT
(without the INTO table part)
I want to do something like this:
select IDENTITY(int,1,1) as ident, * from my_table
How to do that?
I use MS-SQL2000.
Lilian.This is a multi-part message in MIME format.
--=_NextPart_000_0AC4_01C381C4.ABEE7350
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Try:
select
(select count(*) from MyTable as i
where i.PK <=3D o.PK) as ident
, o.*
from
MyTable as o
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message =news:#TlBUTegDHA.1872@.TK2MSFTNGP09.phx.gbl...
Hi all,
I need the same functionality as IDENTITY(int,1,1) but only in a SELECT
(without the INTO table part)
I want to do something like this:
select IDENTITY(int,1,1) as ident, * from my_table
How to do that?
I use MS-SQL2000.
Lilian.
--=_NextPart_000_0AC4_01C381C4.ABEE7350
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
(select =count(*) from MyTable as i
= where i.PK <=3D o.PK) as ident
, =o.*
from
MyTable as o
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Lilian Pigallio" wrote =in message news:#TlBUTegDHA.1872=@.TK2MSFTNGP09.phx.gbl...Hi all,I need the same functionality as IDENTITY(int,1,1) but only =in a SELECT(without the INTO table part)I want to do something =like this: select =IDENTITY(int,1,1) as ident, * from my_tableHow to do that?I use MS-SQL2000.Lilian.

---

--=_NextPart_000_0AC4_01C381C4.ABEE7350--|||this is a workable solution, but why don't you want to use
SELECT INTO with identity function? because solution
suggested by Tom will be very slow if the resultset is
really large. In that case SELECT INTO result table and
then getting selecting from result table will run faster.
>--Original Message--
>Try:
>select
> (select count(*) from MyTable as i
> where i.PK <= o.PK) as ident
>, o.*
>from
> MyTable as o
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:#TlBUTegDHA.1872@.TK2MSFTNGP09.phx.gbl...
>Hi all,
>I need the same functionality as IDENTITY(int,1,1) but
only in a SELECT
>(without the INTO table part)
>I want to do something like this:
> select IDENTITY(int,1,1) as ident, * from my_table
>
>How to do that?
>I use MS-SQL2000.
>Lilian.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C3827D.24033A20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks :^)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23NGj2YegDHA.2072@.TK2MSFTNGP10.phx.gbl...
Try:
select
(select count(*) from MyTable as i
where i.PK <=3D o.PK) as ident
, o.*
from
MyTable as o
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message =news:#TlBUTegDHA.1872@.TK2MSFTNGP09.phx.gbl...
Hi all,
I need the same functionality as IDENTITY(int,1,1) but only in a =SELECT
(without the INTO table part)
I want to do something like this:
select IDENTITY(int,1,1) as ident, * from my_table
How to do that?
I use MS-SQL2000.
Lilian.
--=_NextPart_000_0015_01C3827D.24033A20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks :^)
"Tom Moreau" = wrote in message news:%23NGj2YegDHA.=2072@.TK2MSFTNGP10.phx.gbl...
Try:

select
(select =count(*) from MyTable as i
= where i.PK <=3D o.PK) as ident
, =o.*
from
MyTable as = o
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Lilian Pigallio" =wrote in message news:#TlBUTegDHA.1872=@.TK2MSFTNGP09.phx.gbl...Hi all,I need the same functionality as IDENTITY(int,1,1) but =only in a SELECT(without the INTO table part)I want to do something =like this: select =IDENTITY(int,1,1) as ident, * from my_tableHow to do that?I use MS-SQL2000.Lilian.

---

--=_NextPart_000_0015_01C3827D.24033A20--

Need a little help please

I have a simple table:
MyTable
id int null
user varchar(50) null
desc varchar(150) null
No PK or indexes of any kind.
Does the following cause any performance issue:
insert into MyTable (desc, user, id) values ('Bob from Account
Temps','Bob','777')
Does not having indexes or PK hinder the insert? And does the order of the
columns in the insert make a difference?
Thanks all
JD
Every table should have a primary key. Why do you have a table without
one?

> Does not having indexes or PK hinder the insert?
A lack of indexes and constraints doesn't affect the insert per se.
Potentially it affects the *validity* of the insert since you won't be
prevented from inserting redundant data.

> And does the order of the
> columns in the insert make a difference?
If all the column names are listed then logically the order makes no
difference.
David Portas
SQL Server MVP
|||This is a 3rd party application. I'm trying to gather information what will
be presented at a meeting next week.
thanks for the feedback.
JD
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegr oups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>
|||>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
It can, but the effect will probably only surface under more extreme conditions.
If there is a clustered index, SQL Server will immediately know where to put this row. If the cl ix
key is always increasining (which isn't necessarily the optimal cl ix key for *searches*), then you
won't get page splits either.
If there isn't a cl ix, then SQL Server need to go by IAMs and PFS pages (I believe, it is still
early in the morning for me) to determine where to put the rows. I imagine that this can be more
work, but I doubt we see the effect unless large databases and heavy load.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegr oups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>

Need a little help please

I have a simple table:
MyTable
id int null
user varchar(50) null
desc varchar(150) null
No PK or indexes of any kind.
Does the following cause any performance issue:
insert into MyTable (desc, user, id) values ('Bob from Account
Temps','Bob','777')
Does not having indexes or PK hinder the insert? And does the order of the
columns in the insert make a difference?
Thanks all
JDEvery table should have a primary key. Why do you have a table without
one?
> Does not having indexes or PK hinder the insert?
A lack of indexes and constraints doesn't affect the insert per se.
Potentially it affects the *validity* of the insert since you won't be
prevented from inserting redundant data.
> And does the order of the
> columns in the insert make a difference?
If all the column names are listed then logically the order makes no
difference.
--
David Portas
SQL Server MVP
--|||This is a 3rd party application. I'm trying to gather information what will
be presented at a meeting next week.
thanks for the feedback.
JD
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
> > Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
> > And does the order of the
> > columns in the insert make a difference?
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>|||>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
It can, but the effect will probably only surface under more extreme conditions.
If there is a clustered index, SQL Server will immediately know where to put this row. If the cl ix
key is always increasining (which isn't necessarily the optimal cl ix key for *searches*), then you
won't get page splits either.
If there isn't a cl ix, then SQL Server need to go by IAMs and PFS pages (I believe, it is still
early in the morning for me) to determine where to put the rows. I imagine that this can be more
work, but I doubt we see the effect unless large databases and heavy load.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>> And does the order of the
>> columns in the insert make a difference?
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>

Need a little help please

I have a simple table:
MyTable
id int null
user varchar(50) null
desc varchar(150) null
No PK or indexes of any kind.
Does the following cause any performance issue:
insert into MyTable (desc, user, id) values ('Bob from Account
Temps','Bob','777')
Does not having indexes or PK hinder the insert? And does the order of the
columns in the insert make a difference?
Thanks all
JDEvery table should have a primary key. Why do you have a table without
one?

> Does not having indexes or PK hinder the insert?
A lack of indexes and constraints doesn't affect the insert per se.
Potentially it affects the *validity* of the insert since you won't be
prevented from inserting redundant data.

> And does the order of the
> columns in the insert make a difference?
If all the column names are listed then logically the order makes no
difference.
David Portas
SQL Server MVP
--|||This is a 3rd party application. I'm trying to gather information what will
be presented at a meeting next week.
thanks for the feedback.
JD
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>|||>> Does not having indexes or PK hinder the insert?
> A lack of indexes and constraints doesn't affect the insert per se.
It can, but the effect will probably only surface under more extreme conditi
ons.
If there is a clustered index, SQL Server will immediately know where to put
this row. If the cl ix
key is always increasining (which isn't necessarily the optimal cl ix key fo
r *searches*), then you
won't get page splits either.
If there isn't a cl ix, then SQL Server need to go by IAMs and PFS pages (I
believe, it is still
early in the morning for me) to determine where to put the rows. I imagine t
hat this can be more
work, but I doubt we see the effect unless large databases and heavy load.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108586063.090431.233100@.l41g2000cwc.googlegroups.com...
> Every table should have a primary key. Why do you have a table without
> one?
>
> A lack of indexes and constraints doesn't affect the insert per se.
> Potentially it affects the *validity* of the insert since you won't be
> prevented from inserting redundant data.
>
> If all the column names are listed then logically the order makes no
> difference.
> --
> David Portas
> SQL Server MVP
> --
>