Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

View Help Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 1:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:03 AM
Points: 187, Visits: 422
Hey guys,


So I have this view, and in creating this view, I want to pull all these invoices that were created in the past month...However if I pull back 2 invoices that have different invoice ids but they have the same group id, same subscriber id, and same due date, I just need to pull the max invoice id...How can I do that?

Thanks
Post #1340865
Posted Monday, August 6, 2012 1:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Are you familiar with aggregate functions (like MAX and MIN) in queries? You can use those in Views, just like any other query.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1340868
Posted Monday, August 6, 2012 2:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:03 AM
Points: 187, Visits: 422
Yes I have but just adding MAX to invoice id column does not work...
Post #1340885
Posted Monday, August 6, 2012 2:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 20,792, Visits: 32,703
Unfortunately, we can't see what you see, nor do we know what the final result should look like. Just not enough information to give you a solid answer.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1340886
Posted Monday, August 6, 2012 2:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:03 AM
Points: 187, Visits: 422
Here is a sample result set:

InvoiceID DueDate GroupID SubscriberID
101 1/31/12 4 22222
102 1/31/12 4 22222
103 1/15/12 5 33333


So you see the first 2 records have the same due date, same group id , and same subscriber Id and the 3rd record is completely different...

I want my final result set to be:

InvoiceID DueDate GroupID SubscriberID

102 1/31/12 4 22222
103 1/15/12 5 33333

Post #1340889
Posted Monday, August 6, 2012 2:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:03 AM
Points: 187, Visits: 422
I apologize, I forgot the formatting does not reflect how it was typed in the message...

I remember now that you have to create custom code to reflect what I am asking and it makes it easier for yall to understand ... Unfortunately, I am on a time constraint and dont have time to go through all that so I apologize and will just try to keep working with what i have and see if i can come up with what I am needing...
Post #1340890
Posted Monday, August 6, 2012 2:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 1,931, Visits: 19,885
asm1212 (8/6/2012)
I apologize, I forgot the formatting does not reflect how it was typed in the message...

I remember now that you have to create custom code to reflect what I am asking and it makes it easier for yall to understand ... Unfortunately, I am on a time constraint and dont have time to go through all that so I apologize and will just try to keep working with what i have and see if i can come up with what I am needing...


try this...btw I have the same constraints


USE [tempdb]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst_InvoiceHeader]') AND type in (N'U'))
DROP TABLE [dbo].[tst_InvoiceHeader]
GO


CREATE TABLE [dbo].[tst_InvoiceHeader](
[invoice_id] [int] NULL,
[grouo_id] [int] NULL,
[subscriber_id] [int] NULL,
[duedate] [datetime] NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[tst_InvoiceHeader]([invoice_id], [grouo_id], [subscriber_id], [duedate])
SELECT 101, 4, 22222, '20120131 00:00:00.000' UNION ALL
SELECT 102, 4, 22222, '20120131 00:00:00.000' UNION ALL
SELECT 103, 5, 33333, '20120115 00:00:00.000'

select * from tst_InvoiceHeader

SELECT MAX(invoice_id) AS MaxId
FROM tst_InvoiceHeader
GROUP BY grouo_id, subscriber_id, duedate




______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1340897
Posted Monday, August 6, 2012 2:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 13,182, Visits: 12,672
asm1212 (8/6/2012)

Unfortunately, I am on a time constraint and dont have time to go through all that so I apologize and will just try to keep working with what i have and see if i can come up with what I am needing...


No offense but seriously??? You don't have time to put together enough details to ask your question. Putting together the following sample data took me approximately 2 minutes.

create table MyInvoice
(
InvoiceID int,
DueDate date,
GroupID int,
SubscriberID int
)

insert MyInvoice
select 101, '1/31/2012', 4, 22222 union all
select 102, '1/31/2012', 4, 22222 union all
select 103, '1/15/2012', 5, 33333

select * from MyInvoice

You did a fine job explaining that you want a view and what it should look like. Using the MAX function as suggested produces exactly the results you stated from your sample data.

create view MyInvoiceViewWithMax
as
select MAX(InvoiceID) as InvoiceID, DueDate, GroupID, SubscriberID
from MyInvoice
group by DueDate, GroupID, SubscriberID

go

select * from MyInvoiceViewWithMax

Total time working on the total solution (about 5 minutes including posting the code).


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1340903
Posted Monday, August 6, 2012 3:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:03 AM
Points: 187, Visits: 422
Yeah no offense but my view is much larger than that, and i was trying to dumb it down...

I have tried using the MAX function, but it does not work when i try it...Im selecting about 50+ more columns than what I specified and therefore you have to group by all 50+ columns...so no I dont have the time to try to write up a sample view of what I am trying to accomplish...Man this site used to be a great resource but the last couple of times I have visited, it has become more of nuisance and waste of time...

Thank you all for your time and cooperation!

Post #1340911
Posted Monday, August 6, 2012 3:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 13,182, Visits: 12,672
asm1212 (8/6/2012)
Yeah no offense but my view is much larger than that, and i was trying to dumb it down...

I have tried using the MAX function, but it does not work when i try it...Im selecting about 50+ more columns than what I specified and therefore you have to group by all 50+ columns...so no I dont have the time to try to write up a sample view of what I am trying to accomplish...Man this site used to be a great resource but the last couple of times I have visited, it has become more of nuisance and waste of time...

Thank you all for your time and cooperation!



I apologize if my response sounded a bit snarky. I totally get what you mean that you had to simplify it. And of course once you group by a zillion columns it may no longer return 1 row.

By all means don't write this place off as a solid resource. Those of us that post sometimes get bogged down by the lazy people who just want their answer right now because they are under a time crunch and as a result we can sometimes sound a bit snarkish. Sadly the quality of the help is direct result of the quality of the post. Given that you basically said to ignore it I probably should have. Instead I chose to give you an answer to your simple question and berated you at the same time. My apologies.



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1340912
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse