Showing posts with label select. Show all posts
Showing posts with label select. Show all posts

Thursday, March 29, 2012

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";J et OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MD W";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
Massimo
After other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
sql

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work

distributed partitioned view + procedure

If i have a view such as
Create view Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
And if one server say server 2 is unavailable, will the view fail to run ?
If so , how can i still let the stored proc run
and same for a stored procedure
Create proc Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
What happens in this case if server2 is unavailable ? And also a way to let
it run should any server be made unavailable> And if one server say server 2 is unavailable, will the view fail to run ?
For a query where the optimizer realizes it has to hit server 2 will fail.
> If so , how can i still let the stored proc run
Have redundancy on the servers.
Same goes for stored procedures.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OHe5POhkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> If i have a view such as
> Create view Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
>
> And if one server say server 2 is unavailable, will the view fail to run ?
> If so , how can i still let the stored proc run
> and same for a stored procedure
> Create proc Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
> What happens in this case if server2 is unavailable ? And also a way to let
> it run should any server be made unavailable
>
>

Distributed FULL OUTER JOIN results in INNER join

Hi,
I am trying to perform a full outer join across two linked
servers - LINK1 and LINK2. If I put the following query in:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
- the query plan gives me a MERGE JOIN/FULL OUTER JOIN
which is what I expect. If, however, I add a WHERE clause:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
WHERE C1.r = 0 and C2.r = 0
- I get a MERGE JOIN/INNER JOIN, which gives me the wrong
result. The table definition is:
create table test (n char(8) not null, s int not null, r
tinyint not null)
go
alter table test add primary key (n,s)
go
I have obviously simplified the problem as much as
possible. Am I misunderstanding something about FULL OUTER
JOINS or is this a feature? I can obviously circumvent by
selecting into two temporary tables on the local server
and doing the join there.
regards
KenMany thanks
Ken
>--Original Message--
>The problem is nothing to do with the query being a
distributed one. Because
>your WHERE clause references both sides of the join, the
result set will
>include only the INNER joined rows - WHERE C1.r = 0 AND
C2.r = 0 means that
>both r columns must be non-NULL in the result. Perhaps
what you intended
>was:
>...
>WHERE COALESCE(C1.r, C2.r)=0 AND COALESCE(C2.r, C1.r)=0
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

Tuesday, March 27, 2012

Distinct?

Hi,
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
?
How to do that? I need join just the distinct Id from a table with other
table...
Is it possible to be done ?
You could do this:
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
(select distinct EmployeeID from SomeTable) A join EmpTable B on EmpTable.ID
= A.EmployeeID
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> ?
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>
|||SELECT Distinct SomeTable.EmployeeID ,EmpTable.EmployeeName from
SomeTable
INNER JOIN EmpTable ON SomeTable.EmployeeID = EmpTable.ID
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> ?
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>

Distinct?

Hi,
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
'
How to do that? I need join just the distinct Id from a table with other
table...
Is it possible to be done ?You could do this:
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
(select distinct EmployeeID from SomeTable) A join EmpTable B on EmpTable.ID
= A.EmployeeID
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> '
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>|||SELECT Distinct SomeTable.EmployeeID ,EmpTable.EmployeeName from
SomeTable
INNER JOIN EmpTable ON SomeTable.EmployeeID = EmpTable.ID
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> '
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>sql

Distinct?

Hi,
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
'
How to do that? I need join just the distinct Id from a table with other
table...
Is it possible to be done ?You could do this:
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
(select distinct EmployeeID from SomeTable) A join EmpTable B on EmpTable.ID
= A.EmployeeID
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> '
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>|||SELECT Distinct SomeTable.EmployeeID ,EmpTable.EmployeeName from
SomeTable
INNER JOIN EmpTable ON SomeTable.EmployeeID = EmpTable.ID
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> '
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>

DISTINCT w/ character data

Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:

> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql

DISTINCT Values

I am trying to run a query to one of two delete duplicates records. The
process I normally use is use
1) SELECT DISTINCT from the table into a second table
2) Delete all duplicate values in original table
3) Copy the disctinct values from the second table back into the original
table
Unfortunately, this time, my table has ntext fields in it. SELECT DISTINCT
does not work with ntext fields.
Does anyone have an alternative solution?
Thank you,
JLFlemingHave you thought about declaring a PRIMARY KEY?|||I have thought about it. I cannot declare a primary key if there are alread
y
duplicates in the table. Once I get rid of duplicates, I can put a primary
key in.
"--CELKO--" wrote:

> Have you thought about declaring a PRIMARY KEY?
>|||http://www.aspfaq.com/2431
http://www.aspfaq.com/2509
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:FAF44802-6129-435D-B7AE-EBBC0C042ECD@.microsoft.com...
> I am trying to run a query to one of two delete duplicates records. The
> process I normally use is use
> 1) SELECT DISTINCT from the table into a second table
> 2) Delete all duplicate values in original table
> 3) Copy the disctinct values from the second table back into the original
> table
> Unfortunately, this time, my table has ntext fields in it. SELECT
> DISTINCT
> does not work with ntext fields.
> Does anyone have an alternative solution?
> Thank you,
> JLFleming

Sunday, March 25, 2012

Distinct Selection in SELECT

Hi all, How can I achieve this?
select col1,distinct(col2),col3,col4 from table1
TIAI have no idea what you are trying to achieve.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:#fgDiYlLFHA.2380@.TK2MSFTNGP10.phx.gbl...
> Hi all, How can I achieve this?
> select col1,distinct(col2),col3,col4 from table1
>
> TIA
>|||--Record present
Col1 Col2 Col3
mark 1qa 5/5/2003
mohan 2dc 1/1/2004
jerry cvse 12/31/2002
john cvse 12/31/2002
SELECT Col1,Distinct(Col2),COl3 From Table1
-- desired recordset
Col1 Col2 Col3
mark 1qa 5/5/2003
mohan 2dc 1/1/2004
jerry cvse 12/31/2002
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:egU%23UalLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> I have no idea what you are trying to achieve.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:#fgDiYlLFHA.2380@.TK2MSFTNGP10.phx.gbl...
>|||Can you explain why jerry and not john? And if it were like this:
mark 1qa 20030505
melody 1qa 20020608
jerry cvse 20021231
john cvse 20030104
What would the output be then?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:OadtVhlLFHA.2384@.tk2msftngp13.phx.gbl...
> --Record present
> Col1 Col2 Col3
> mark 1qa 5/5/2003
> mohan 2dc 1/1/2004
> jerry cvse 12/31/2002
> john cvse 12/31/2002
> SELECT Col1,Distinct(Col2),COl3 From Table1
> -- desired recordset
> Col1 Col2 Col3
> mark 1qa 5/5/2003
> mohan 2dc 1/1/2004
> jerry cvse 12/31/2002
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:egU%23UalLFHA.3868@.TK2MSFTNGP10.phx.gbl...
>|||I guess sql does a max like top..or something..I am not sure
though that's what I am trying to achieve I want distinct on col2 yet want
to select the other cols too...how can I accomplish it?
TIA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uIhTtllLFHA.700@.TK2MSFTNGP10.phx.gbl...
> Can you explain why jerry and not john? And if it were like this:
> mark 1qa 20030505
> melody 1qa 20020608
> jerry cvse 20021231
> john cvse 20030104
> What would the output be then?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:OadtVhlLFHA.2384@.tk2msftngp13.phx.gbl...
>|||> I guess sql does a max like top..or something..I am not sure
> though that's what I am trying to achieve I want distinct on col2 yet
want
> to select the other cols too...how can I accomplish it?
You still haven't defined what you want to accomplish. You want one row for
every col2. However, if there are multiple different values for col1 and
col3, you need to define how SQL Server will determine which ones to
include.
If you do not care, say so. "ANY col1 / col3 will do." Maybe one of these
is what your after, though I question the usefulness of the extra columns if
this is the case:
SELECT MAX(Col1), Col2, MAX(col3)
FROM Table1
GROUP BY Col2
(Notice that Col1 will not necessarily come from the same row as Col3, which
is why I asked about the date AND the name that you wanted returned.)
If you do care, say so. Provide REAL specs (not typing out your data in
tabular format) as suggested in http://www.aspfaq.com/5006 (please read in
full, including the link to generate insert statements) and we can provide a
real solution.
A|||great! works for me
Thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uhGB69lLFHA.3788@.tk2msftngp13.phx.gbl...
> want
> You still haven't defined what you want to accomplish. You want one row
for
> every col2. However, if there are multiple different values for col1 and
> col3, you need to define how SQL Server will determine which ones to
> include.
> If you do not care, say so. "ANY col1 / col3 will do." Maybe one of
these
> is what your after, though I question the usefulness of the extra columns
if
> this is the case:
> SELECT MAX(Col1), Col2, MAX(col3)
> FROM Table1
> GROUP BY Col2
> (Notice that Col1 will not necessarily come from the same row as Col3,
which
> is why I asked about the date AND the name that you wanted returned.)
> If you do care, say so. Provide REAL specs (not typing out your data in
> tabular format) as suggested in http://www.aspfaq.com/5006 (please read in
> full, including the link to generate insert statements) and we can provide
a
> real solution.
> A
>sql

distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?
How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com

distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

sql

Distinct question

Hi
Ive got a problem with the Distinct function
I need to only select one column with the distinct function and leave the
rest so they can have duplicates.
For exampel
Table_Orders
OrderID | Orderdate | User | City
Then I want to select the unique OrderID to list all current orders but at
the same time i want the users and the orderdates no to be unique. so i want
the distinct function to work only on the OrderID column.
Is that possible ?
/GustafThis has been responded to in the .programming newsgroup.
Please do not multi-post.
Thanks!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gurra" <gurgel@.telia.com> wrote in message
news:umLGpRPnDHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi
> Ive got a problem with the Distinct function
> I need to only select one column with the distinct function and leave the
> rest so they can have duplicates.
> For exampel
> Table_Orders
> OrderID | Orderdate | User | City
> Then I want to select the unique OrderID to list all current orders but at
> the same time i want the users and the orderdates no to be unique. so i
want
> the distinct function to work only on the OrderID column.
> Is that possible ?
> /Gustaf
>
>

DISTINCT QUERY PLEASE HELP

I do an distinct query like this:
query = "SELECT DISTINCT name, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"
The query works fine but I need to select one more field called "info" so I just tried this:
query = "SELECT DISTINCT name, info, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"
but then I get the error message:
"Column 'products.info' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
I want do group just by "name" and not "info"
How can I select this "info" field to?? somebody know??

Hi,
You can use
Select name, max(info), total=count........ Group by name
To "fool" the Sql server, but I am not sure this is what you want.
Will the info column have the same value for all the rows with the samevalue in the name column? Then you might as well use group by name,info the result willbe the same.
But if the info column has different values within the same name, which one will you have displayed?
BTW: It should not be necessary to use distinct in this query
|||

Now i do realize I cant get the info field because its just a distinct query. Forget this message.
thanks anyway

distinct query for table containing records more than 69347

What would be the best way to avoid distinct clause in query to get the result.My Query looks like

select distinct Col1 ,col2,col3,col4 from ABC where (1=1) group by Col1 ,col2,col3,col4 order by col1

ABC Contains records like

Col1 col2 col3 col4

a 1 1 1

a 2 1 1

B 2 2 2

B 1 1 1

;

;

;

how is the performance of disticnt query on Number of Rows ? HOw can i Improve my query performance if want to use distinct in my query? will adding indexes help to boost performance?

Thanks

You didn't tell us what is your desired outcome...

The WHERE clause can be removed.

The GROUP BY forces what you display.

Do you wish to collapse so that all 'a'/'B', etc is in one row?

If so, then something like:

Code Snippet


SELECT
Col1,
col2 = sum( Col2 ),
Col3 = sum( Col3 ),
Col4 = sum( Col4 )
FROM ABC
GROUP BY Col1

|||There is no reason you need both Distinct AND group by that I know of. How is the performance of the query as you have it? And why the where (1=1)... Is this part of a larger generated query?

Distinct Query

When i run this query,
select distinct(firstname + ' ' + lastname), firstname, lastname, department
from tbltest

it brings out 149 records

but when i run it with the extension field

select distinct(firstname + ' ' + lastname), firstname, lastname, department, extension
from tbltest

Is there like a limit to the distinct query on the number of columns returned ?first query

select distinct(firstname + ' ' + lastname), firstname, lastname, department
from tbltest

2nd query
select distinct(firstname + ' ' + lastname), firstname, lastname, department, extension
from tbltest

hi,

r u getting a different no of records when the 2nd query is executed?

if so, the fields SELECTed by the second query will not be DISTINCT (with extension added to the SELECT list)

Distinct Query

Hi everybody,

I would like to select all "id" in a table ONLY where "raff" is distinct.

I think it shoult be such a thing:

SELECT id FROM mytable WHERE (SELECT DISTINCT raff) or

SELECT id FROM (SELECT DISTINCT raff FROM mytable)

So what is the correct syntax

Thnak U

BaRRonThis sounds familiar

select id = max(id)
from tbl
group by raff
having count(*) = 1