January 16, 2009 at 4:18 am
hi friends.
i have a distinct problem with view.
--
that is my view:
ALTER VIEW [dbo].[b]vwPurchase[/b]
AS
Select
pur.InPurchaseId,
pur.StNotes,
Convert(varchar(20),pur.DtProcDTime,104) as StProcDTime,
ac1.StAccountName,
ac2.StAccountName as StCompanyName,
CAST(pur.BoIsActive AS varchar(5)) AS BoIsActive
from
TbPurchase as pur
INNER JOIN
TbAccounts as ac1 On ac1.InAccountsId=pur.InOurRelevantId
INNER JOIN
TbCompanies as comp ON comp.InCompaniesId=pur.InCompanyId
INNER JOIN
TbAccounts as ac2 ON ac2.InAccountsId=comp.InAccountsId
i tried to distinct but, i could not. for solve problem i create a new view as like below
ALTER view [dbo].[b]vwPurchase2[/b]
as
Select distinct InPurchaseId,StCompanyName,StAccountName,StProcDTime,BoIsActive,StNotes from vwPurchase
but nothing changed.
query of 'select * from vwPurchase' and ' select * from vwPurchase2' have same result. please tell me how can i distinct vwPurchase view.
Regard all friends
January 16, 2009 at 4:47 am
I am not totally sure what the problem is, can you describe what you want to achieve?
Also be aware that distinct means "return me only rows that have a difference "in any of the columns retrieved" - so if there is any difference in any of the columns you are retrieving you will get all the rows.
Mike John
January 16, 2009 at 4:56 am
Without any tabel description, sample data and desired output, it's hard to guess where the problem really is.
You can use DISTINCT in Views, so that's not the problem. So it's either you might understand DISTINCT differently or there is some kind of a flaw in the data.
Resolving that, we need to know what's happening in your database.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 16, 2009 at 4:56 am
DISTINCT is going to give the distinct values for the combination of following columns:
InPurchaseId,StCompanyName,StAccountName,StProcDTime,BoIsActive,StNotes
i.e the distinct value of the combination of columns in the SELECT clause
Hope you are looking for same, if not then please provide details about what output you are looking.
-Vikas Bindra
January 16, 2009 at 5:05 am
Firstly thank you very much friends.
these datas a pieace of my view. see 8,9 coloums. they have same datas. but i cant distinct
vwPurchase
1Güven Büro Mobilya Ahmet ÇEVIK24.04.20071
7Kadiköy Elektrik Malz.Iml.Tic.Ltd.Sti.Ahmet ÇEVIK25.04.20071
8Adomat Matbaa Baski Ltd Sti.Emre Güzeldal11.05.20071
9Adomat Matbaa Baski Ltd Sti.Emre Güzeldal11.05.20071
how can i distinct??
January 16, 2009 at 5:15 am
Its because purchase Id is different. Omit purchase ID in the second view and try
January 16, 2009 at 5:35 am
cotzan (1/16/2009)
Firstly thank you very much friends.these datas a pieace of my view. see 8,9 coloums. they have same datas. but i cant distinct
8Adomat Matbaa Baski Ltd Sti.Emre Güzeldal11.05.20071
9Adomat Matbaa Baski Ltd Sti.Emre Güzeldal11.05.20071
They don't have the same data. The purchaseID is different. Which one do you want to see?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2009 at 5:39 am
i want to see first one with its ID
January 16, 2009 at 5:53 am
A MIN() and a GROUP BY should do the work, something like
Select DISTINCT
MIN(pur.InPurchaseId)
pur.StNotes,
Convert(varchar(20),pur.DtProcDTime,104) as StProcDTime,
ac1.StAccountName,
ac2.StAccountName as StCompanyName,
CAST(pur.BoIsActive AS varchar(5)) AS BoIsActive
from
TbPurchase as pur
INNER JOIN
TbAccounts as ac1 On ac1.InAccountsId=pur.InOurRelevantId
INNER JOIN
TbCompanies as comp ON comp.InCompaniesId=pur.InCompanyId
INNER JOIN
TbAccounts as ac2 ON ac2.InAccountsId=comp.InAccountsId
GROUP BY
pur.StNotes,
pur.DtProcDTime,
ac1.StAccountName,
ac2.StAccountName,
pur.BoIsActive
January 16, 2009 at 5:58 am
cotzan (1/16/2009)
i want to see first one with its ID
WITH IDD
AS
(Select ROW_NUMBER() OVER (PARTITION BY StCompanyName ORDER BY StCompanyName) AS IDNR, StCompanyName,StAccountName,StProcDTime,BoIsActive,StNotes FROM vwPurchase)
SELECT * FROM IDD
WHERE IDNR =1
January 16, 2009 at 6:14 am
January 16, 2009 at 6:19 am
There is the test:
--CREATING TABLE JUST FOR TEST
CREATE TABLE AAA
(
ID INT IDENTITY(1,1),
PLACE NVARCHAR(40),
DATE DATETIME
);
--JUST TO ADD SOME DATE VALUE IN FORMAT DD/MM/YYYY
SET DATEFORMAT DMY
--INSERTING VALUES IN TABLE
INSERT INTO AAA
SELECT 'ISTANBULL', '16/01/2009'
UNION ALL
SELECT 'PARIS', '17/01/2009'
UNION ALL
SELECT 'ISTANBULL', '16/01/2009'
UNION ALL
SELECT 'LONDON', '16/01/2009'
UNION ALL
SELECT 'OSLO', '16/01/2009'
--CREATING THE TEST VIEW
CREATE VIEW BBB
as
WITH IDD
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY PLACE ORDER BY PLACE) AS NR, * FROM AAA)
SELECT * FROM IDD
WHERE NR = 1;
-- SELECTING THE DATA JUST TO VERIFY THE RESULTS
SELECT ID, PLACE, DATE FROM BBB
ORDER BY ID
--CLEAR TEST
DROP TABLE AAA;
DROP VIEW BBB;
:hehe:
January 16, 2009 at 6:41 am
i did like you as..
ALTER VIEW [dbo].[vwPurchase]
AS
Select distinct
min(pur.InPurchaseId),
pur.StNotes,
Convert(varchar(20),pur.DtProcDTime,104) as StProcDTime,
ac1.StAccountName,
ac2.StAccountName as StCompanyName,
CAST(pur.BoIsActive AS varchar(5)) AS BoIsActive
from
TbPurchase as pur
INNER JOIN
TbAccounts as ac1 On ac1.InAccountsId=pur.InOurRelevantId
INNER JOIN
TbCompanies as comp ON comp.InCompaniesId=pur.InCompanyId
INNER JOIN
TbAccounts as ac2 ON ac2.InAccountsId=comp.InAccountsId
group by
pur.StNotes,
pur.DtProcDTime,
ac1.StAccountName,
ac2.StAccountName ,
pur.BoIsActive
but it give error that : 'Create View or Function failed because no column name was specified for column 1.'
January 16, 2009 at 8:34 am
i did like that..
ALTER VIEW [dbo].[vwPurchase]
AS
Select distinct
min(pur.InPurchaseId),
pur.StNotes,
Convert(varchar(20),pur.DtProcDTime,104) as StProcDTime,
ac1.StAccountName,
ac2.StAccountName as StCompanyName,
CAST(pur.BoIsActive AS varchar(5)) AS BoIsActive
from
TbPurchase as pur
INNER JOIN
TbAccounts as ac1 On ac1.InAccountsId=pur.InOurRelevantId
INNER JOIN
TbCompanies as comp ON comp.InCompaniesId=pur.InCompanyId
INNER JOIN
TbAccounts as ac2 ON ac2.InAccountsId=comp.InAccountsId
group by
pur.StNotes,
pur.DtProcDTime,
ac1.StAccountName,
ac2.StAccountName ,
pur.BoIsActive
but it give error that : 'Create View or Function failed because no column name was specified for column 1.'
January 16, 2009 at 8:50 am
As the error says, there's no name for the first column. A view needs all columns named.
ALTER VIEW [dbo].[vwPurchase]
AS
Select
min(pur.InPurchaseId) AS MinPurchaseID,
pur.StNotes,
...
You don't need the distinct any longer, as the group by will remove duplicate rows itself.
p.s. no need to bump your post after an hour because you haven't got a reply. This is a forum, not a chatroom.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply