Title Page Background
DEBUG FN args: Array ( [0] => 758 [1] => [2] => [3] => agent ) BEFORE: Site: 758 LocID: Region: State: agent Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 758 Igate: njhud LocID: 758 Region: State: Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 758 AND i.igate IN ('inhud','ushud') AND m.expertType = 'agent' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'inhud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1; DEBUG FN args: Array ( [0] => 758 [1] => [2] => [3] => lender ) BEFORE: Site: 758 LocID: Region: State: lender Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 758 Igate: njhud LocID: 758 Region: State: Experttype: lender Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 758 AND i.igate IN ('inhud','ushud') AND m.expertType = 'lender' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'inhud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1; DEBUG FN args: Array ( [0] => 758 [1] => [2] => [3] => other ) BEFORE: Site: 758 LocID: Region: State: other Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 758 Igate: njhud LocID: 758 Region: State: Experttype: other Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 758 AND i.igate IN ('inhud','ushud') AND m.expertType = 'other' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'inhud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1;

3 Orchard Street
Great Neck, NY 11023

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,888,000
Beds: 4
Baths:
Status: Active
SqFt: --
Agency: Mitra Hakimi Realty Group LLC

Map Details Show Map | Show StreetView




House Features

Appliances: Dishwasher, Washer, Refrigerator, Dryer
Architectural Style: Colonial
Construction Materials: Brick, Stucco, Frame
Flooring: Hardwood
Heating: Forced Air
Interior Features: Entrance Foyer, Pantry, Storage, Eat-in Kitchen, MASTER BATH, Den/Family Room, Formal Dining
Rooms: Bathroom 1, Bathroom 2, Bathroom 3, Bedroom 1, Bedroom 2, Bedroom 3, Bedroom 4

Window Features

Exterior Features: Sprinkler System
Parking Features: Garage, Driveway, Garage - Attached

Neighborhood/Schools

Elementary School/District: John F Kennedy School/Great Neck
Middle/Junior High School/District: /Great Neck
High School/District: Great Neck North High School/Great Neck
Subvidision Name:

Description/Comments


Lot Size: 0 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2009
Notes: Discover suburban luxury living at 3 Orchard Street, nestled in the desirable community of Great Neck, NY. This meticulously crafted residence, built in 2009 and located in a quiet low traffic cul-de-sac, offers an exquisite blend of modern comfort and timeless elegance. Enter this home through the open and bright entry foyer which leads to the expansive living room for comfortable gatherings and further into the elegant formal dining room perfect for hosting dinner parties. Enjoy the gourmet eat in kitchen with stainless steel appliances as well as ample counter and cabinet space. The kitchen opens into a cozy den with a gas fireplace ideal for relaxation or entertainment. Also on the first floor you will find a convenient laundry room and half bathroom for added practicality. On the second floor you will find the master suite which provides ample closet space, bathroom with two sinks, stand up shower and jacuzzi! 3 additional bedrooms and another full bathroom located on the second floor. The home further provides large recreational space in the basement as well as storage space in the attic with potential to convert to a living space. The home also offers a private patio and backyard area for BBQ & outdoor enjoyment. Enjoy central air conditioning and heating system for year-round comfort controlled by smart thermostats for energy efficiency and convenience. Central vacuum system for effortless cleaning. Location of this home is unbeatable ! Home is a short walk to houses of worship as well as Middle and High Schools. Home is also close to Parkwood Sports Complex, the Village Green Park and Memorial Field! Easy access to public transportation as well- N58 bus stop to Great Neck town and train station. Express LIRR train service from Great Neck to NYC- Penn Station (25 minutes) and Grand Central Station (31 minutes)! Nestled in the heart of a charming neighborhood, this stunning house boasts a prime location, offering convenient access to local amenities. Don't miss the opportunity to make this exceptional property your new home!
MlsNumber: --


Listing Provided By: Mitra Hakimi Realty Group LLC, original listing
Phone: (347) 581-2352
Office Phone: (718) 268-5588
Agent Name: Mitra Hakimi
Disclaimer: Copyright © 2024 OneKey MLS. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


Local Real Estate Expert

DEBUG FN args: Array ( [0] => njhud [1] => 0 [2] => noble [3] => IN [4] => agent [5] => 1 ) BEFORE: Site: njhud LocID: 0 Region: noble State: IN Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 IFELSE else AFTER: Site: njhud Igate: njhud LocID: 0 Region: noble State: IN Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 758 AND i.igate IN ('inhud','ushud') AND m.expertType = 'agent' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'inhud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1;
Real Estate Expert Photo for David Ley
David Ley
ASCIIHosler Realty Inc - Kendallville
Call Today!: (260) 226-9741

USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App