| Author |
Topic |
|
erichk34
Starting Member
8 Posts |
 Posted - 2011-06-16 : 16:00:22
|
| I need to know if there is a way to select a random set of records by zipcode... however my need is really to chain to a file of 300 zip codes with desired quantities beside each zip....can i refer to that file during an extract and pull those desire quantities...file would be zip quantity45678 34534789 19023433 9022etc |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
erichk34
Starting Member
8 Posts |
Posted - 2011-06-16 : 16:34:57
|
| inner join to a seperate file where only 2 fields exist ...zip and desired quantity for that zip...So i need to extract data from a date file and the result would be only the zip codes in the inner joined file and only the amount of records that were in the desired quanitityy column....hope that makes sense |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
erichk34
Starting Member
8 Posts |
Posted - 2011-06-16 : 16:41:10
|
| I have never used T-SQL before. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
erichk34
Starting Member
8 Posts |
Posted - 2011-06-16 : 17:00:00
|
| SQL SERVER 2008 |
|
|
|
erichk34
Starting Member
8 Posts |
Posted - 2011-06-16 : 17:00:38
|
| Sorry ..not up on lingo yet |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
erichk34
Starting Member
8 Posts |
Posted - 2011-06-16 : 17:14:21
|
| which file? there are 2... My database of 11 million records of which i want ot extract from. The second file is the zip code file that contains 2 fields ..Zip,desired quantity.I need to extract data from the 11 million records by zip ..but i only need the desired quanities in the second column in the second file |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
erichk34
Starting Member
8 Posts |
Posted - 2011-06-16 : 17:34:54
|
| both are tables ..imported into SQLone is the actual database containing data.. and the other is the 2 fields i have been speaking about... zip code and the desired quantity my customer would like for the zip code .. there are 400 zip codes with variable quantities listed so it has a higher level of difficulty for me. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
erichk34
Starting Member
8 Posts |
Posted - 2011-06-16 : 19:46:03
|
| Let me try again ....I have a table with 11 million records in it with various fields. One of those fields is zip code.I have another table that contain 400 zip codes with a desired quantity (count) zip desired qty98212 3,45676332 2,111etc....in my file of 11 million records i have thousands of records per zip ... but i only want to extract the desired qty from the table inner joining by zip.So in my 11 million record table... i have a count for zip code 98212 of 78,995 ,, but i only want the qty specified in the other table ..which for that zip code is 3,456.so instead of writing 400 different select top 'INSERT QTY HERE" queries per zip code.. i was wondering if when i am inner joining by zip code i can also tell the query to bring in the desired quantity and only pull that many records.If you dont get it now .. then i give up .. this is my first time in a forum and i thought i explained it fairly well. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-17 : 07:23:35
|
The query I am posting below has two problems - (but having problems with queries has never before stopped me from posting, so why stop now?)First: I am sort of kind of guessing what you are looking for.Second: this is not going to be very efficient with 11 million rows.;WITH CTE AS( SELECT *,ROW_NUMBER() OVER (PARTITION BY zip ORDER BY (SELECT NULL)) AS RN FROM YourTableWith11MillionRecords)SELECT c.*FROM CTE c INNER JOIN DesiredQuantityTable d ON d.zip = c.zipWHERE c.RN <= d.[desired qty] Oh, another thing - which may or may not be a problem: This will work only SQL 2005 or later. |
|
|
|
|
Multiple quantities per zip